none
VBA to put large canned email into body of email message RRS feed

  • Question

  • I want to send the same email message to a list of 140 emails. Each email will have the same attachment. I have the code that will do that. BUT...the text that I want to put in the body of the email is over 4,000 characters and contains several paragraphs. 

    I tried putting the text in Excel and copying it to the email, but the results do not look right. I can manually copy the body from Word and paste into an open email, but I don't know how to do this with VBA. 

    How do I do this and maintain the appearance of the text?

    tod

    Monday, August 10, 2015 8:57 PM

Answers

  • The simplest approach is to use http://www.gmayor.com/ManyToOne.htm   in one to one mode. The page explains how to use Word as the body of your message (personalised or not) with your attachment.

    However it is fairly straightforward to edit the body of an Outlook message in VBA, as recent versions of Outlook all have a Word-like editor that can be programmed pretty much as Word itself. The following code can be run from Excel and should point the way forward:

    Option Explicit
    
    Sub SendMessage()
    Dim oOutlookApp As Object
    Dim oItem As Object
    Dim olInsp As Object
    Dim wdDoc As Object
    Dim oRng As Object
    
        On Error Resume Next
        'Get Outlook'
        Set oOutlookApp = GetObject(, "Outlook.Application")
    
        If Err <> 0 Then
            MsgBox "Start Outlook first!"
            GoTo lbl_Exit
        End If
    
        'Create a new mailitem'
        Set oItem = oOutlookApp.CreateItem(0)
        With oItem
            .BodyFormat = 2
            Set olInsp = .GetInspector
            Set wdDoc = olInsp.WordEditor
            Set oRng = wdDoc.Range
            oRng.Collapse 1
            'Do what you want here with orng, which is the message start (before the signature) e.g.'
            oRng.Text = "This is the message body"
            .To = "someone@somewhere.com"
            .Subject = "This is the subject"
            .Attachments.Add "C:\Path\Filename.pdf"
            .Display
        End With
    
        'Clean up'
        Set oItem = Nothing
        Set oOutlookApp = Nothing
        Set olInsp = Nothing
        Set wdDoc = Nothing
    lbl_Exit:
        Exit Sub
    End Sub
    
    


    Graham Mayor - Word MVP
    www.gmayor.com

    • Proposed as answer by André Santo Tuesday, August 11, 2015 10:46 AM
    • Marked as answer by todtown Wednesday, August 12, 2015 6:49 PM
    Tuesday, August 11, 2015 7:10 AM

All replies

  • What email client do you use?
    Tuesday, August 11, 2015 12:45 AM
  • I normally use Windows Live Mail, but am using Outlook for this task. 

    I think I have a solution, although I'd still be interested to know if there is a better way.

    I copied the email from a Word document and pasted special to use the Excel format. That put each paragraph in a different cell. Then in the VBA code I concatenate the cells together with two vbcrlfs between the cells values. Put all that into a string variable and use the variable as the body. When the code puts it into the Outlook message, Outlook even formats my hyperlink and everything.

    I have a new problem but I'll wait until tomorrow to tackle it. This may require a new thread, but as long as I'm here...when this is complete it will be sending the email to over 140 addresses. I did a test where I sent the email to my email address five times. However, in the first test only three of the emails came through. In the second test only two of them came through. Do I need to put a pause in my loop for each iteration? Any ideas on that?

    Thanx for the reply,

    Tod

    Tuesday, August 11, 2015 1:04 AM
  • The simplest approach is to use http://www.gmayor.com/ManyToOne.htm   in one to one mode. The page explains how to use Word as the body of your message (personalised or not) with your attachment.

    However it is fairly straightforward to edit the body of an Outlook message in VBA, as recent versions of Outlook all have a Word-like editor that can be programmed pretty much as Word itself. The following code can be run from Excel and should point the way forward:

    Option Explicit
    
    Sub SendMessage()
    Dim oOutlookApp As Object
    Dim oItem As Object
    Dim olInsp As Object
    Dim wdDoc As Object
    Dim oRng As Object
    
        On Error Resume Next
        'Get Outlook'
        Set oOutlookApp = GetObject(, "Outlook.Application")
    
        If Err <> 0 Then
            MsgBox "Start Outlook first!"
            GoTo lbl_Exit
        End If
    
        'Create a new mailitem'
        Set oItem = oOutlookApp.CreateItem(0)
        With oItem
            .BodyFormat = 2
            Set olInsp = .GetInspector
            Set wdDoc = olInsp.WordEditor
            Set oRng = wdDoc.Range
            oRng.Collapse 1
            'Do what you want here with orng, which is the message start (before the signature) e.g.'
            oRng.Text = "This is the message body"
            .To = "someone@somewhere.com"
            .Subject = "This is the subject"
            .Attachments.Add "C:\Path\Filename.pdf"
            .Display
        End With
    
        'Clean up'
        Set oItem = Nothing
        Set oOutlookApp = Nothing
        Set olInsp = Nothing
        Set wdDoc = Nothing
    lbl_Exit:
        Exit Sub
    End Sub
    
    


    Graham Mayor - Word MVP
    www.gmayor.com

    • Proposed as answer by André Santo Tuesday, August 11, 2015 10:46 AM
    • Marked as answer by todtown Wednesday, August 12, 2015 6:49 PM
    Tuesday, August 11, 2015 7:10 AM
  • Excel allows up to 32,767 characters in a cell.  Can you try this method here?

    http://www.rondebruin.nl/win/s1/outlook/amail6.htm

    Change this line:

    .Body = "Hi " & cell.Offset(0, -1).Value

    Make that be whatever you want.

    That should do it for you.


    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

    Wednesday, August 12, 2015 6:44 PM