Excel/VBA/Outlook Integration Help

[teraman]

Hi,

I am attempting to create a script that draws from an excel sheet of 'clients', with respecitive primary and secondary contacts for each (including emails). Unfortuantely, the email being sent out is relatively standard, but one unique attachment needs to be made to each client, this will be done manually.

As of now, I am able to draw email address from Excel columns and create new emails to send out, but I am having trouble copying in a large amount of text for the body of the message. So far I have:

Sub Test1()
    Dim OutApp As Object
    Dim OutMail As Object
    Dim cell As Range
    Application.ScreenUpdating = False
    Set OutApp = CreateObject("Outlook.Application")
    On Error GoTo cleanup
    For Each cell In Columns("B").Cells.SpecialCells(xlCellTypeConstants)
        If cell.Value Like "?*@?*.?*" And _
           LCase(Cells(cell.Row, "C").Value) = "yes" Then
            Set OutMail = OutApp.CreateItem(0)
            On Error Resume Next
            With OutMail
                .To = cell.Value
                .Subject = "Reminder"
                .Body = "Dear " & Cells(cell.Row, "A").Value _
                      & vbNewLine & vbNewLine & _
                        "Please contact us to discuss bringing " & _
                        "your account up to date"
                            .Display  'Or use Send
            End With
            On Error GoTo 0
            Set OutMail = Nothing
        End If
    Next cell
cleanup:
    Set OutApp = Nothing
    Application.ScreenUpdating = True
End Sub

Which basically creates a standard email using the emails in Column B and respecitive names in A. Right now it only displays a short one sentence message, but I am looking to add in about a Word documents worth of text into each email.

Lorem ipsum dolor sit amet, consectetur adipiscing elit. Nullam eget nisi tincidunt, pellentesque nulla imperdiet, pulvinar lectus. Donec id pharetra velit. Vivamus pretium molestie arcu eu sodales. Maecenas aliquam dui purus, nec lobortis justo vehicula quis. Morbi nisi lacus, rhoncus sit amet ipsum sed, sodales euismod nulla. Fusce dui nisl, porttitor vitae mi vel, ullamcorper sagittis neque. Phasellus non placerat ipsum, nec elementum enim. Nam tempus imperdiet purus. Phasellus cursus libero sodales ligula pellentesque hendrerit. Suspendisse ut sagittis tortor. Donec vehicula tortor sed purus tincidunt ornare. In hac habitasse platea dictumst. Vestibulum sed porta arcu. Sed fringilla magna sed accumsan interdum.

Aenean et tortor sed diam suscipit imperdiet. Maecenas ac lobortis justo, in vulputate metus. Quisque at mi ut nulla sagittis semper. Cras adipiscing enim nunc, a lacinia lectus eleifend sagittis. Proin molestie mauris in libero volutpat tincidunt. Morbi nec elit ipsum. Sed odio nibh, tincidunt bibendum dapibus at, pulvinar pulvinar lorem. Fusce condimentum lacus leo, et varius eros rhoncus a. Vestibulum magna augue, dictum vitae faucibus auctor, blandit in enim. Etiam fringilla odio non turpis varius, non hendrerit risus blandit. Curabitur orci erat, rhoncus eu arcu sed, consequat porttitor est. Proin vulputate at libero consectetur luctus. Aliquam volutpat quis lorem sit amet hendrerit.

Sed luctus ligula neque, sed luctus tellus dictum ut. Pellentesque quis orci eget tellus suscipit scelerisque. Integer vitae nulla gravida ipsum hendrerit dapibus. In eu justo eu nisl placerat rhoncus. Duis viverra pulvinar nisl eu ultrices. Curabitur mattis commodo tortor, a tincidunt nibh adipiscing nec. Sed elementum, sapien a sollicitudin ullamcorper, metus quam venenatis turpis, nec dictum elit lorem a urna.

Nulla mattis a purus aliquet viverra. Suspendisse id posuere tellus. Ut at ornare orci. Etiam tincidunt orci enim, ac auctor augue dignissim at. Quisque auctor semper nunc, sit amet semper leo venenatis quis. Donec quis ornare metus. Vestibulum cursus libero sed tincidunt dictum. Duis tristique, justo sed pulvinar congue, eros metus pharetra orci, non rhoncus lorem dui vel ipsum. Praesent ante lorem, mollis id blandit nec, commodo at nibh. Etiam vel consequat turpis. Nulla facilisi. Aliquam sit amet convallis felis. Aenean tempus tortor ornare metus sollicitudin, at facilisis quam condimentum. Nullam id urna dolor. Pellentesque adipiscing nunc auctor sapien commodo, quis commodo orci scelerisque.

Integer ultricies arcu tellus, quis ultrices nulla pretium at. Sed scelerisque interdum laoreet. Ut vel purus blandit, placerat risus at, laoreet nisl. Vivamus ut odio euismod, vestibulum purus eu, vehicula diam. Phasellus pellentesque urna ut quam pellentesque luctus. Nullam sagittis semper neque in luctus. Pellentesque habitant morbi tristique senectus et netus et malesuada fames ac turpis egestas. Sed commodo rutrum nisl, non congue augue bibendum sit amet. Nam vitae imperdiet nulla. Aliquam venenatis odio neque, a posuere sapien cursus a. Nullam faucibus nulla tellus, in bibendum velit consectetur in. Maecenas facilisis consequat lectus, eu suscipit tellus volutpat eu.

Above is about the amount of text that will need to be imported into Body of the email. Are there any easier ways to import this rather than hard coding the message into the VBA script? Possibly using a copy/paste approach? Also another question, is it possible to change the .From field so it appears that the message is being sent from a distribution list as opposed to a single person who will be running the script?


Thanks very much for the help.

 

[s.ridd]

If you want to go down a copy/paste route then this post demonstrates how to achieve the paste part using the .WordEditor property http://www.mrexcel.com/forum/excel-questions/703421-visual-basic-applications-paste.html

however rather than keeping the text you need on the clipboard you could perhaps create a template. This page has more details on this method.
CreateItemFromTemplate Method

Hope this helps

Simon

 

[Wookiee]

I've had some experience with sending emails from an Excel macro. While it's not pretty, my suggestion would be to hard-code each paragraph as string variables and then have one last variable combine them all.

So, for example:

Dim strDoubleSpace As String: strDoubleSpace = vbNewLine & vbNewLine

Dim strPar1 As String: strPar1 = "Example Paragraph 1: Et cetera..." & strDoubleSpace
Dim strPar2 As String: strPar2 = "Example Paragraph 2: Ad infinitum." & strDoubleSpace
Dim strPar3 As String: strPar3 = "Example Paragraph 3: Ad nauseum."

Dim strMsg As String: strMsg = strPar1 & strPar2 & strPar3

...

          .Body = "Dear " & Cells(cell.Row, "A").Value _
                & strDoubleSpace & strMsg

As far as changing the .From parameter, I believe you can set it so that it appears to come from a different sender's address, but unless you've got your email account linked to the email account from which you want to appear to be sending, the receiver will still see your actual email address.

PS. I couldn't help but Google Translate your Latin text example. I have to say I got a chuckle when I found that "Phasellus pellentesque urna ut quam pellentesque luctus" translated into "We stick to the pot smoking drugs."

 

[s.ridd]

Didn't spot your last question, the property you're looking for there is .SentOnBehalfOfName

 

[teraman]

Thanks very much for the replies guys, very helpful. And I also thought that translation was quite funny :D

 

[teraman]

I've had some experience with sending emails from an Excel macro. While it's not pretty, my suggestion would be to hard-code each paragraph as string variables and then have one last variable combine them all.

So, for example:

Dim strDoubleSpace As String: strDoubleSpace = vbNewLine & vbNewLine

Dim strPar1 As String: strPar1 = "Example Paragraph 1: Et cetera..." & strDoubleSpace
Dim strPar2 As String: strPar2 = "Example Paragraph 2: Ad infinitum." & strDoubleSpace
Dim strPar3 As String: strPar3 = "Example Paragraph 3: Ad nauseum."

Dim strMsg As String: strMsg = strPar1 & strPar2 & strPar3

...

          .Body = "Dear " & Cells(cell.Row, "A").Value _
                & strDoubleSpace & strMsg

As far as changing the .From parameter, I believe you can set it so that it appears to come from a different sender's address, but unless you've got your email account linked to the email account from which you want to appear to be sending, the receiver will still see your actual email address.

PS. I couldn't help but Google Translate your Latin text example. I have to say I got a chuckle when I found that "Phasellus pellentesque urna ut quam pellentesque luctus" translated into "We stick to the pot smoking drugs."

Hi Wookie,

This worked great, I am just wondering if there is any way to keep formatting within the text (bold, underline, color, etc)?

Thanks again

 

[Wookiee]

Hi Wookie,

This worked great, I am just wondering if there is any way to keep formatting within the text (bold, underline, color, etc)?

It's probably possible, but I'm afraid that is outside of my realm of experience. I did some googling before responding and it looks to me like it would require either referencing the Outlook object model or using the Word object model to manipulate the body text in the Outlook object. Unfortunately, there's no macro recorder for Outlook. The best advice I can offer is to take this bit of code I put together by recording a macro in Word and see if you can wedge it into your existing code maybe. I could get it to work for me when running from the Word VBA Editor, but not when I tried it from Excel.

Word VBA

'Underline Portions
Selection.Find.ClearFormatting
With Selection.Find
    .Text = "underlined"
    .Forward = True
    .Wrap = wdFindContinue
End With
Selection.Find.Execute
Selection.Font.Underline = wdUnderlineSingle

'Bold Portions
Selection.Find.ClearFormatting
With Selection.Find
    .Text = "bold"
    .Forward = True
    .Wrap = wdFindContinue
End With
Selection.Find.Execute
Selection.Font.Bold = True

'Italic Portions
Selection.Find.ClearFormatting
With Selection.Find
    .Text = "italic"
    .Forward = True
    .Wrap = wdFindContinue
End With
Selection.Find.Execute
Selection.Font.Italic = True

Also, here are a couple of links that might help:

Use Word Macro to Apply Formatting to Outlook Email - Slipstick Systems

How to Search for a String in an Outlook Email Message and Automate a Reply that Contains the String - Office and Office 365 dev blog - Site Home - MSDN Blogs

Finally, I'd like to suggest playing around with s.ridd's suggestion to use a template. If there's a way to use Excel VBA to generate the email and then import a template with all your text already formatted, that might be preferable.

Good luck!

 

[teraman]

Hey Wookie,

Thanks again for following up and the detailed response. No worries on not have worked with specific formatting before, what you already helped me with was more than enough. I will definitely look into the above though, but I have seem to been coming across the same issues that you have regarding possibly referencing the Outlook object model or go to the Word macro route. Regardless, I'm going to look through everything you posted up and see what I can come up with. Again, really appreciate the help and reply. Will let you guys know if I come up with anything of interest.

 

[teraman]

Apologies for the double post, I just came across another small problem and am wondering if you guys may have some advice. When sending my emails out I have a list of Primary and Secondary contacts, these will both be going into the CC field. Unfortunately, some of the secondary contacts are unpopulated and I have labeled them with 'NO CONTACT LISTED'. Now I can get strings into the CC field using something along the lines of

.CC =Cells(cell.Row, "A").Value & Cells(cell.Row, "B").Value

I have been messing around with some conditional statements to try to just leave the field blank when it comes across 'NO CONTACT LISTED' but have been unable to come up with any results. Any help would be much appreciate.

Thank you again.

 

[Wookiee]

Would something like this code work?

    .CC = Replace((Cells(cell.Row, "A").Value & "; " & Cells(cell.Row, "B").Value), "NO CONTACT LISTED", ";")