sending email with template

[Armitage2k]

Hello, i have no clue what I am doing

...alright, since I read that at the "How to talk to a programmer" thread, I think it is a necessary thing to point out right at the beginning. I honestly just started with Access 2 days ago and built a rather easy database, but I ran into a small problem now.

I need some help please concerning sending an email from Access (via Outlook) to an email address saved in the table, BUT with a template message which is saved in an external textfile. Besides, the email shall be opened and not being sent immediately. The email address is pulled from the entry which was open when I clicked the email button.

Private Sub SendEmail1_Click()
Dim stDocName, stLinkCriteria, Subject, Message, Template As String

stTemplate = "C:\template\EmailMessage.txt"
stSubject = "Warm greetings!"

    If IsNull([Email]) Or ([Email]) = "" Then
       MsgBox "There is no E-mail address entered for this person!"
       Exit Sub
    Else
       stLinkCriteria = Me![Email]
       DoCmd.SendObject acSendNoObject, , , stLinkCriteria, , , stSubject, stMessage, True, stTemplate
    End If
End Sub

I found a code example for mass-mailing where a template is used and sent to all entries of a database. However, I am not familiar enough with VBA/Access to convert this to one entry and preview only. http://www.paulsadowski.com/wsh/massmailer.htm

I know now that the template function of the SendObject function is not usable for this matter, thus need a workaround for this somehow please.
Also, whenever I close the email, hence do not want to send it, Access starts complaining that the code was interrupted and wants me to either debug or end the macro.

If someone could point me in the right direction to get this fixed, I would highly appreciate it.
Thank you very much for your time and efforts,
A2k

 

[GaryPanic]

try this

Private Sub Command13_Click()

Dim EmailApp, NameSpace, EmailSend As Object

Set EmailApp = CreateObject("Outlook.Application")
Set NameSpace = EmailApp.GetNamespace("MAPI")
Set EmailSend = EmailApp.CreateItem(0)

EmailSend.To = [Forms]![claimsreporter]![text8] ****** this will have your email address in it *******
EmailSend.Subject = "Claims Report for" & " " & [Forms]![claimsreporter]![Text3] ****** this will have your message**********
'EmailSend.Message = "h:\email.txt" (THIS FAILED)
EmailSend.Attachments.Add "C:\temp\cancellation.xls" ***** any attachments that are required ****
EmailSend.Display ***** will not send it opens up you have to push send*****

Set EmailApp = Nothing
Set NameSpace = Nothing
Set EmailSend = Nothing

'Kill "C:\temp\test2.xls"

End Sub3


now the disclaimers - none of this is my own work any thing with between the *** and in cluding the ** needs to be removed

this works on Access 2000
you might need to laod the correct Outlook libary

 

[Armitage2k]

thanks for the reply.

I get a runtime error while executing, saying that a typeconflict is present and cannot be translated to outlook. I tried 2 different approaches of defining the email address but none works

Private Sub SendEmail_Click()
Dim EmailApp, NameSpace, EmailSend As Object

Set EmailApp = CreateObject("Outlook.Application")
Set NameSpace = EmailApp.GetNamespace("MAPI")
Set EmailSend = EmailApp.CreateItem(0)

[COLOR="Red"]EmailSend.To = Me![Email] ' recipients email address in the table[/COLOR]
' EmailSend.To = [Forms]![Profile]![Email] (did not work either)
EmailSend.Subject = "Warm Greetings from me!" ' subject line of the email
EmailSend.Message = "C:\email.txt"
EmailSend.Attachments.Add "C:\attachment.xls" ' path of the attachment, if needed
EmailSend.Display ' Opens up outlook and displays the email, does not send it automatically

Set EmailApp = Nothing
Set NameSpace = Nothing
Set EmailSend = Nothing

'Kill "C:\temp\attachment.xls"
End Sub

thanks for the help!
A2k

 

[GaryPanic]

don't use me. ---- use the form name and ensure that the fields on the form are these names -
so you data name might zigzag - but the form name might be txt72
ensure right libary is installed - this works a treat - it creats an email and attaches a file