Advanced Mail Merge Question

[adam.greer]

Hi Guys

I know this isn't strictly access but I'm running out of ideas.

I have a merge from my DB to Word and then Word to Outlook. It works quite well but there are a few things I would like to implement to save time for my users.

1) After the data has been merged with word is there a way to automatically fill in the subject field and send it as an email to outlook i.e macro/scrpit in word, access commands

2) Can I have an automatic way of chaning who the mail is sent from. I use my DB to send emails to artists to tell them they are going to be played on our playlists. Each show has a different email (the presenters email). The current method I use is to have my user change his default mail address in outlook to the presenters then do the merge.

But he then has to wait for all the mails to send, then change the default again and repeat. This can turn a 2 min job into 30 mins because of the waiting. It also adds a human error potential of selecting the wrong mail address.


Any ideas what I can do to remedy these? Perhaps a decent third-party add-in?

Thanks

Adam Greer

 

[Moniker]

A quick search of this forum probably would've turned up this, which explains how to do what you want.

You need to connect to an Outlook Object and from there, you have complete programmatic control.

 

[adam.greer]

I did find that but it's not what I'm looking for. I need to use word because I use alot of images, atleast I need use of HTML which I didn't think you can do this way right?

The main reason for my post was the ability to change what outlook account or 'reply to' settings are used for each mail.

Also I need to send the mails out to 30sh people at once and not one per click.


Any other ideas anyone?

Adam Greer

 

[Moniker]

There's no reason you can't use HTML from the example given -- just put the HTML code in the .body section and if your email is set up to use HTML by default, you're all set. To email to multiple people with a single click, you can loop through something similar to the example, changing the recipients and the body as necessary. You can alter the reply to person using the ReplyRecipients collection, which is exposed and accessed the same way that .Recipients, .Body, etc. are shown in the example.

 

[adam.greer]

I have the code working as you first displayed it, but I am having trouble with some of the fields.

For example I can't put my HTML directly in the code becasue it's quite long and contains alot of " which interferes with the code.

Secondly I'm having trouble trying to reference something from a form/table/query within it. For example I wanted the subject to be from a form.

    Dim OLApp As New Outlook.Application
    Dim OLMsg As Outlook.MailItem
    Dim strBody As String
    Dim strSubject As String
    
    strBody = "Test"
    
    strSubject = "You're On " & [Form].[playlists].[PartnerName]
    
    Set OLMsg = OLApp.CreateItem(olMailItem)
    With OLMsg
        .Recipients.Add "adam.greer@pulserated.com"
        .Subject = strSubject
        .Body = strBody
        .Send
    End With
    
    MsgBox "Emailing Complete", vbOKOnly + vbInformation, "Email Sent"
    
    Set OLApp = Nothing
    Set OLMsg = Nothing

I receive an error saying it cannot find the field. Everything is spelt correctly however.

Any clues to what I am doing wrong here?

 

[boblarson]

Change

strSubject = "You're On " & [Form].[playlists].[PartnerName]

To

strSubject = "You're On " & [B]Forms!playlists.PartnerName[/B]

 

[Moniker]

For the quotes ("), use chr(34) to insert it.

strBody = "The word " & chr(34) & "quotes" & chr(34) & " is in quotes."

Returns: The word "quotes" is in quotes.

You can also store the entire body of your email in a Memo field and just reference that.

 

[adam.greer]

Thanks for your help guys.

Everything has worked perfectly.