+ Reply to Thread
Results 1 to 7 of 7

Send excelsheet in the body of an email using vba

Hybrid View

Nancy123 Send excelsheet in the body... 09-28-2010, 03:56 AM
DonkeyOte Re: Send excelsheet in the... 09-28-2010, 05:07 AM
Nancy123 Re: Send excelsheet in the... 09-28-2010, 08:39 AM
JBeaucaire Re: Send excelsheet in the... 09-28-2010, 10:01 AM
Nancy123 Re: Send excelsheet in the... 09-28-2010, 10:23 AM
Nancy123 Re: Send excelsheet in the... 09-30-2010, 06:55 AM
Nancy123 Re: Send excelsheet in the... 09-30-2010, 07:06 AM
  1. #1
    Registered User
    Join Date
    09-06-2010
    Location
    Bristol
    MS-Off Ver
    Excel 2003
    Posts
    96

    Send excelsheet in the body of an email using vba

    Hi guys
    The following code sends an attachment with the email to different recipients. Is it possible instead of attaching the sheet with the email, Just include it in the body part of the email. I mean where we write other text like "Hello How r u? " and then the data of sheet11 will come after it.
    Public Function SendOneSheet(a As String)
    
        Dim olApp As Outlook.Application
        Dim olMail As MailItem
    
        Set olApp = New Outlook.Application
        Set olMail = olApp.CreateItem(olMailItem)
    
        ThisWorkbook.Worksheets("sheet11").Copy
     
        ActiveWorkbook.SaveAs ThisWorkbook.Path & "\" & _
            "MI.xls"
    
        With olMail
           .Recipients.Add " "
                     
            .Subject = a
            .Body = "Please find attached the spreadsheet showing the information" & vbCrLf
            .Attachments.Add ActiveWorkbook.FullName
            .Send
        End With
    MsgBox "The email has been sent"
        ActiveWorkbook.Close False
    
        Kill ThisWorkbook.Path & "\" & "MI.xls"
    
        Set olMail = Nothing
        Set olApp = Nothing
    
    End Function
    Last edited by Nancy123; 09-30-2010 at 07:06 AM.

  2. #2
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Send excelsheet in the body of an email using vba

    see: http://www.rondebruin.nl/sendmail.htm

    specifically the section Outlook object model (body)

  3. #3
    Registered User
    Join Date
    09-06-2010
    Location
    Bristol
    MS-Off Ver
    Excel 2003
    Posts
    96

    Re: Send excelsheet in the body of an email using vba

    I tried the link but it was again sending the sheet as an attachment with the email. Can anyone please send me the code for this .

    Thanks a lot.

  4. #4
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Send excelsheet in the body of an email using vba

    The link was to a list of other pages. The one you want to try is here:

    http://www.rondebruin.nl/mail/folder3/mail4.htm
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  5. #5
    Registered User
    Join Date
    09-06-2010
    Location
    Bristol
    MS-Off Ver
    Excel 2003
    Posts
    96

    Re: Send excelsheet in the body of an email using vba

    Hi It is working if there is only one recipient but if there are multiple recipients then instead of including the names in Cc or Bcc, how can we include the all names in To section.

    Thanks so much.

  6. #6
    Registered User
    Join Date
    09-06-2010
    Location
    Bristol
    MS-Off Ver
    Excel 2003
    Posts
    96

    Re: Send excelsheet in the body of an email using vba

    Hi All...Can anyone please figure out my problem?

  7. #7
    Registered User
    Join Date
    09-06-2010
    Location
    Bristol
    MS-Off Ver
    Excel 2003
    Posts
    96

    Re: Send excelsheet in the body of an email using vba

    Thanks guys... My problem has been resolved...

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1