CODEWITHSUNDEEP
ms-accessoutlookvbams-access-2010
james.sw.clark
james.sw.clark

Reputation: 357

MS Access - Automatic email templates with Outlook

I have attempted to modify the code in Sorceri's answer on StackOverflow, which demonstrates how to send an email using outlook. I tried to modify this to provide a custom email template, specific to the selected entry in the database:

Private Sub Emailtemplatebtn_Click()

Dim App As Outlook.Application
Dim Mail As MailItem
Set App = CreateObject("Outlook.application")

Set Mail = oApp.CreateItem(olMailItem)
oMail.Body = "Dear" &[Forename]& "," &vbCrLf& &vbCrLf& "It has been six months or longer since I last contacted you. Have there been any big gains with regard to impact?"
oMail.Subject = &[Project name]& "- check up"
oMail.To = &Email&
oMail.Send
Set oMail = Nothing
Set oApp = Nothing

End Sub

Unfortunately, the code doesn't work. The in-built VBA editor highlights the lines "oMail.Body", "oMail.subject", and "oMail.to" in red.

I figure that this could be a very helpful feature, as it will save users even more time - the script/database will essentially handle all of the user's copy and pasting!

Solution

After using "Option Explicit", it seemed to be easier to locate why the code was failing - variables were not properly defined (I added some "o"s, and took some away). Here is the finished code:

  1. Put "Option Explicit" at the top of the VBA page for the form (the module for the form)

  2. Use the following code, largely corrected by Andre.

    Private Sub Emailtemplatebtn_Click()

    Dim App As Outlook.Application
Dim oMail As MailItem
Set App = CreateObject("Outlook.application")


Set oMail = App.CreateItem(olMailItem)
oMail.Body = "Dear" & Me![Forename] & "," & vbCrLf & vbCrLf & "It has been six months or longer since I last contacted you. Have there been any big gains with regard to impact?"
oMail.Subject = Me![Project name] & "- check up"
oMail.To = Me!Email
oMail.Send
Set oMail = Nothing
Set App = Nothing

End Sub

Change oMail.send to oMail.display if you wish to make edits to the email before sending.

Upvotes: 0

Views: 3034

Answers (1)

Andre
Andre

Reputation: 27634

You want

Dim oMail As MailItem

(note the "o")

And the syntax for & is variable & "string", note the spaces and don't use two & in a row.

Those lines should be:

oMail.Body = "Dear" & [Forename] & "," & vbCrLf & vbCrLf & "It has been six months or longer since I last contacted you. Have there been any big gains with regard to impact?"
oMail.Subject = [Project name] & "- check up"
oMail.To = Email

If the variables are controls on your form, it is better to write Me!Forename or Me![Project name] so Access knows where to look for them.

Upvotes: 2

Related Questions