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]

I'm still of the opinion that if your text doesn't change that using a template would be much easier than hard coding the text in vba. However, if you aren't going to access the word editor to copy and paste the text, using it to alter your already hard coded text seems unnecessary.

If you really want to type all the text out in code and format it you should consider replacing .Body with .HTMLBody

something like this:

.HTMLBody = "<p><b>This text is Bold</b> <u>this text is Underlined</u> <i>this text is Italic</i></p>" & _        "<p>this text is in a new paragraph <font size = 5 color = red>this text is bigger and red</font></p>

There are plenty of other tags too, I guess Google would be the best place to look, here is a start HTML Tutorial - Formatting

Hope this helps

Simon

 

[daverunt]

If you store the message in an Excel sheet already formatted and use the RangetoHTML function. It will keep the formatting.
Mail Range/Selection in the body of the mail


I think the limitation is that the formatting must be cell wide so you can't have mixed formats in a single cell.
So for example 1 cell could contain a line of bold and the next cell a line of coloured text etc. and you just copy the cells as a range.
It depends on how much you need to do.

 

[teraman]

Would something like this code work?

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

Awesome wookie, this works great. Thank you again for your help. Also in regards to the formatting I think I may mess around with just making g a template as all of the bodies are going to be standard, worst come to worst I can use HTMLbody, both very good suggestions though and I really appreciate the responses, will let you know how it works!

Thanks again.

 

[Wookiee]

Teraman, I'm glad I was able to help. I find that I still learn new things each day and there are definitely some methods posted here in this thread that I'd like to take some time to play around with so that I can utilize them in my future projects.

Cheers!

 

[teraman]

Yes, I think I've finally completed for the most part! Thanks to you guys

Now I have got it all running, when I run the script all 100+ new email threads come up at once. Can you think of an easy way so maybe I could parse that number and only work on 10 emails at a time, instead of having 100+ outlook windows open heh.

 

[Wookiee]

As long as you're confident in the emails the macro is producing, I think if you replace .Display with .Send that you won't have to worry about manually sending each message.

 

[teraman]

As long as you're confident in the emails the macro is producing, I think if you replace .Display with .Send that you won't have to worry about manually sending each message.

The only problem is, each email needs to have a unique file attached to it before sending it out, which needs to be done manually, otherwise I would use .Send. I've been trying to think of a way to only bring up a dozen or so at a time but have not been able to come up with any reasonable solutions.

 

[daverunt]

There are many ways of attaching unique files to each e-mail so I would look at that possibility.

 

[Wookiee]

There are many ways of attaching unique files to each e-mail so I would look at that possibility.

Yeah, I was going to suggest (if it's feasible) to include the full file name including path in a separate column and then use the cell reference for that row. So, for example, say you were storing the file names of attachments for each recipient in column "F":

.Attachments Add Range(cell.Row, "F").Value​

Hope that helps.

 

[James Snyder]

Should you decide to go with the .HTMLBody route, another great resource is CSS Tutorial. This link will allow you to play with the HTML and CSS to get your code working before sticking it back into VBA.