MS Access -> Mailmerge To Email (OnBehlfOf)

[1DMF]

Hi,

I need to add an email-shot function to our MS Access DB, I can automate all this no problem, create the mailmerge template, do all the VB coding to automate this with a datasource etc..

However, normally this would automatically send through the users outlook that is loggen on at the time, no probs.

However, they want me to create a sepcific email address for this mailing campaign and then send via that email addy.

Now creating the mailbox and giving people 'send on behalf' privilages is not a problem, but how do you tell the mailmerge via the VBcode to use xyz@mydomain.com as the senders email address (ie. send on behalf of).

Here is the code I have so far...

Set wordApp = CreateObject("Word.Application")
'Open document
wordApp.Documents.Open sFile

'issue emailmerge commands
With wordApp.ActiveDocument.MailMerge
   .MainDocumentType = wdEMail
   .OpenDataSource Name:=cDrive & "\mypath\mydatasource.xls", _
   ReadOnly:=True, Connection:=sTable, SubType:=wdMergeSubTypeWord, _
   SQLStatement:="Select * From [" & sTable & "]"
   .Destination = wdSendToEmail
   .MailSubject = sSubject
   .Execute
End With

so does anyone know where in the 'With' operation I can issue the OnBehalfOf email address?

Is this even possible?

Also I need to put a dynamic Hyperlink in the document, but I don't seem to be able to use the insert hyperlink and then include mergefields in the url.

how would you do this?

cheers, 1DMF.


"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!

 

[1DMF]

*bump* - can anyone help?

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!

 

[pyoungmwl]

This is the way I've done it....

' Open Outlook for our own device..
Set MyOutlook = New Outlook.Application

' Create the e-mail
Set MyMail = MyOutlook.CreateItem(olMailItem)
' Address it
MyMail.To = DLookup("ParamSendTo", "tblParameters")
MyMail.SentOnBehalfOfName = DLookup("ParamSendFrom", "tblParameters")
' Subject
MyMail.Subject = Subjectline
' Body
MyMail.Body = MyBodyText
' Attachment
MyMail.Attachments.Add FileName
' Send it to Outlook
MyMail.Display
'Cleanup
Set MyMail = Nothing
'Uncomment the next line if you want Outlook to shut down when its done.
'Otherwise, it will stay running.
'MyOutlook.Quit
Set MyOutlook = Nothing

 

[1DMF]

Thanks for the reply but I think you have mis-understood what i'm trying to do.

I am not trying to send an email via outlook, this is a mailmerge via WORD which is using the merge-to-email functionality.

I can send emails with or without attachments no problem, but i need to change the SendOnBehalfOfName via the WORD object before I execute the mailmerge.

From my trawling of the internet I don't think this is possible, the only solution people seem to have is running a mailmerge one at a time for each recipient and then creating an email and attaching the generated merged word doc.

This is not an acceptable solution, this creates a text/html email with a word doc attachment, this is not the same as using a word mailmerge template and merging to email which creates an HTML email from the word doc template.

I'm just going to have to tell the Marketing guy it just isn't possible to do what he wants.

UNLESS I create a domain logon and exchange mailbox for the dummy email address and they then run the database while logged on as that user, but that then defeats the object of wanting to send from an email address that doesn't exist (do_not_reply@mydomain.com).

I just can't see how I can acheive what they want without spending weeks writing some conveluted code to genearte an email from the mailmerge but not send, view the source , copy the code, generate an outlook email object and then copy the merged HTML code to the body of the outlook object and send that way, then I get access to the property SendOnBehalfOfName

unless of course someone else has got a bright idea (here's hoping!)

regards,
1DMF.

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!