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.

 

[Wookiee]

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.

I know this is Teraman's thread, but thanks for the tip, James. I've been testing email creation using HTML tags and some information I found in the HTML section of the tutorial site you referenced, and it seems much easier to me than trying to utilize Outlook or Word VBA to manipulate the text.

At this point, I don't have many Excel macros which generate emails, but it's nice to know if I want to add some specialized formatting, I can do so, and with relative ease.

 

[James Snyder]

Working on adding a sig with a logo to a trio of automated reports right now, so it is fresh...

 

[teraman]

I know this is Teraman's thread, but thanks for the tip, James. I've been testing email creation using HTML tags and some information I found in the HTML section of the tutorial site you referenced, and it seems much easier to me than trying to utilize Outlook or Word VBA to manipulate the text.

At this point, I don't have many Excel macros which generate emails, but it's nice to know if I want to add some specialized formatting, I can do so, and with relative ease.

Yeah definitely thanks for the link James, very helpful. I ended up going the .HTMLBody route which ended up working great.

As for the unique attachments, I will need to talk to the end users regarding that to find out exactly what they're trying to acheive.

In the meantime, do you think you guys can think of anyway of staggering these emails to come out a dozen or so at a time? I have few ideas in my head but most seem to involve changing how the script works completely and revamping it to loop through only a certain # of rows, deleting or marking as sent, something along those lines....have't come up with a solid method yet though.

Thanks again for the help

 

[James Snyder]

Since you are doing it by HTML, one of the options is a send time. I would need to look it up for details, but a loop to build the send time sounds easier than restructuring the code.

EDIT: My apologies! I am using CDO for sending email and it is part of the config structure of CDO.

 

[James Snyder]

A link for the CDO property:
TimeSent Property (Message Object)

 

[James Snyder]

Sorry, but my memory is faulty. Apparently only Outlook allows the delayed sending. Here is a listing of the CDO fields available:
http://schemas.microsoft.com/cdo/configuration/

 

[teraman]

I am still having trouble figuring out a solution to this. I have tried implementing another For loop and creating a counter but am still running into errors. I am honestly not sure if this is possible, but any ideas would be much appreciated.

Thanks again