Consulting

Results 1 to 4 of 4

Thread: Excel sheet to outlook - VBA

  1. #1
    VBAX Newbie
    Joined
    Dec 2019
    Posts
    1
    Location

    Excel sheet to outlook - VBA

    Hello,

    I help your help and experience to help me solve a thing. I have an excel file with some values and formatting done and I have a macro that sends that info to the mail body, but I also have to put 2 or 3 images in that same mail body.
    Is this even possible??


    Thanks in advance for the replies

  2. #2
    VBAX Guru Kenneth Hobs's Avatar
    Joined
    Nov 2005
    Location
    Tecumseh, OK
    Posts
    4,956
    Location
    Welcome to the forum! The answer is yes.

    The two methods in Outlook are either htmlBody or Word Editor. Ron de Bruin explains the methods with examples at: https://www.rondebruin.nl/win/s1/outlook/mail.htm

    In the htmlBody method, add html code tags like "img src". In the Word Editor method, add the images to a range, and then copy and paste using Word methods.

  3. #3
    If you want to insert images in the body of the text as opposed to attaching them, then you can use the Word document inspector to process the message body with VBA as if it was a Word document without having to worry about HTML tags e.g. the following is based on a code I have posted previously, with the addition of two images.

    It is basically a matter of setting the ranges to where you want the texts and images. In this example there is text, followed by the Excel range, followed by text, then an image, more text, a second image, and again more text. Note the comments in the code!

    Sub SendWorkBook()
    'Graham Mayor - https://www.gmayor.com - Last updated - 02 Dec 2019
    'This macro requires the code from
    'http://www.rondebruin.nl/win/s1/outlook/openclose.htm
    'to open Outlook
    
    
    Dim oOutlookApp As Object
    Dim oItem As Object
    Dim olInsp As Object
    Dim wdDoc As Object
    Dim oRng As Object, oImg As Object
    Dim xlRng As Range
    Const strImg1 As String = "C:\Path\Before.png" 'The first image path
    Const strImg2 As String = "C:\Path\After.png" 'The second image path
    
    
    Set xlRng = Range("$I$22:$M$36")    'The range to be copied
    
    
    xlRng.Copy    'Copy it
    
    
    Set oOutlookApp = OutlookApp()    'Use the function from http://www.rondebruin.nl/win/s1/outlook/openclose.htm
        'to open Outlook, or it will not work correctly
    
    
        'Create a new mailitem
        Set oItem = oOutlookApp.CreateItem(0)
        With oItem
            'Address the message
            .To = "someone@somewhere.com"
            'Give it a title
            .Subject = "This is the subject"
            .BodyFormat = 2    'html
            Set olInsp = .GetInspector
            Set wdDoc = olInsp.WordEditor    'access the message body for editing
            Set oRng = wdDoc.Range
            oRng.collapse 1    'set a range to the start of the message
            
            oRng.Text = "This is the message text before the Excel range:" & vbCr & vbCr
            'Collapse the range to its end
            oRng.collapse 0
            
            oRng.Paste
            
            oRng.End = wdDoc.Tables(1).Range.End + 1
            oRng.collapse 0
            oRng.Text = "This is the text after the Excel range." & vbCr & vbCr
            'The range will be followed by the signature associated with the mail account
    
    
            'add the first image at the end of the message before the signature
            
            oRng.collapse 0
            oRng.InlineShapes.AddPicture (strImg1)
    
    
            Set oImg = wdDoc.Bookmarks("_MailAutoSig").Range
            oImg.Start = oImg.Start - 1
            oImg.collapse 1
            oImg.Text = vbCr & vbCr & "This is the text after the first image" & vbCr & vbCr
            oImg.collapse 0
            'add second image
            oImg.InlineShapes.AddPicture (strImg2)
    
    
            Set oImg = wdDoc.Bookmarks("_MailAutoSig").Range
            oImg.Start = oImg.Start - 1
            oImg.collapse 1
            oImg.Text = vbCr & vbCr & "This is the text after the second image" & vbCr & vbCr
    
    
            'display the message - this line is required even if you then add the command to send the message
            .Display
            '.Send
        End With
    
    
        'Clean up
        Set oItem = Nothing
        Set oOutlookApp = Nothing
        Set olInsp = Nothing
        Set wdDoc = Nothing
        Set xlRng = Nothing
        Set oRng = Nothing
        Set oImg = Nothing
    lbl_Exit:
        Exit Sub
    End Sub
    Graham Mayor - MS MVP (Word) 2002-2019
    Visit my web site for more programming tips and ready made processes
    http://www.gmayor.com

  4. #4
    VBAX Newbie
    Joined
    Dec 2019
    Posts
    1
    Location
    good day gmayor,

    please i require your help with the outlook mail auto referencing number, would like to know if to edit anything in the code as i have no knowledge on how to use VBA.

    Best regards

Tags for this Thread

Posting Permissions

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