Results 1 to 3 of 3

Thread: Copying range of Excel cells into body of email via VBA
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Dec 2013
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Copying range of Excel cells into body of email via VBA

    Hi - I have written a macro to send an email (with attachment) to named recipients. It all works fine except that it won't copy the range of excel cells into the body of the email.

    I have highlighted the part that is not working in bold below. The range "email_narrative" is the predefined excel range of data in the source workbook which I want to paste in the body of the email.

    Any ideas why this is not working?




    Sub emailsender()

    Dim SplitsTo As String
    Dim SplitsCC As String
    Dim AllocationsTo As String
    Dim AllocationsCC As String
    Dim TempFileName As Variant
    Dim Tempmessage As Variant

    'Splits Distribution List
    SplitsTo = Sheets("Checks").Range("email_to").Value
    SplitsCC = Sheets("Checks").Range("email_cc").Value
    TempFileName = Sheets("Checks").Range("email_subject").Value
    Tempmessage = Sheets("Checks").Range("email_narrative").Value

    Dim OutlookApp As Object
    Dim OutlookMail As Object
    Set OutlookApp = CreateObject("Outlook.Application")
    Set OutlookMail = OutlookApp.CreateItem(0)
    On Error Resume Next
    With OutlookMail
    .To = SplitsTo
    .Cc = SplitsCC
    .Subject = TempFileName
    .Body = Tempmessage
    .Attachments.Add Application.ActiveWorkbook.FullName
    .Display
    End With
    Set OutlookMail = Nothing
    Set OutlookApp = Nothing
    End Sub

  2. #2
    Administrator starl's Avatar
    Join Date
    Aug 2002
    Location
    Everywhere!
    Posts
    5,859
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Copying range of Excel cells into body of email via VBA

    Body is a string and you're trying to place an array in it. Not compatible.
    You need to get the html that creates the table you would get from a manual copy/paste. You do this by copying the range to a new workbook, publishing that range to a temp html file, then copying the html to your email. There's also a solution to copy/paste with the clipboard, but that can get iffy, depending on programs running in the background interfering with the clipboard.

    Ron de Bruin has an example setup on his site. The relevant piece is the RangeToHTML code:
    https://www.rondebruin.nl/win/s1/outlook/bmail2.htm

  3. #3
    Board Regular
    Join Date
    Mar 2016
    Posts
    222
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Copying range of Excel cells into body of email via VBA

    I'm guessing your email_narrative range has multiple cells. If so, Tempmessage ends up being a 2-dimensional array. Pasting the array as a string into the Body doesn't work.

    One workaround is the following (modified as necessary):
    Code:
    Sub emailsender()
    
    Dim SplitsTo As String
    Dim SplitsCC As String
    Dim AllocationsTo As String
    Dim AllocationsCC As String
    Dim TempFileName As Variant
    Dim Tempmessage As Variant
    
    'Splits Distribution List
    SplitsTo = Sheets("Checks").Range("email_to").Value
    SplitsCC = Sheets("Checks").Range("email_cc").Value
    TempFileName = Sheets("Checks").Range("email_subject").Value
    
    Dim narrativeCell As Range
    For Each narrativeCell In Sheets("Checks").Range("email_narrative")
        If Tempmessage = "" Then
            Tempmessage = narrativeCell.Value
        Else
            Tempmessage = Tempmessage & vbCrLf & narrativeCell.Value
        End If
    Next
    
    Dim OutlookApp As Object
    Dim OutlookMail As Object
    Set OutlookApp = CreateObject("Outlook.Application")
    Set OutlookMail = OutlookApp.CreateItem(0)
    On Error Resume Next
    With OutlookMail
    .To = SplitsTo
    .Cc = SplitsCC
    .Subject = TempFileName
    .Body = Tempmessage
    .Attachments.Add Application.ActiveWorkbook.FullName
    .Display
    End With
    Set OutlookMail = Nothing
    Set OutlookApp = Nothing
    End Sub
    Additional features might include addressing the cells that are empty and not appending them.
    Hobby: VBA and some .Net
    Career: the world of patent and trademark protection

    - forum use guidelines, forum rules and terms of use
    - Try searching for your answer first, see how
    - Read the FAQs
    - List of BB codes

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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