Auto Email Template

[Godber]

Hi,

So far i have managed to link access to outlook via a hyperlink on the email address, click it and it brings a blank email with just the 'To' field populated.

What i want to do is have a generic email template, so when the email address is chosen (via a form which looks at a table) then it loads the template up in outlook with that email address in the too box.

The template would have some variables that i would like to link to other fields within the form also.

I hope this is possibleand if someone could help me it would be great.

Thanks

Liam

 

[TerryHogarth21]

How about something like this?

You just need to adjust the string to create variables based on your form and the names on your forms.

Also be sure to have the Microsoft Outlook Object Library Reference checked.

Function Email()

'******begin code******
Dim Email As String
Dim ref As String
Dim origin As String
Dim destination As String
Dim notes As String

'**create variables for Outlook
Dim objOutlook As Outlook.Application
Dim objEmail As Outlook.MailItem

'**gathers information from your form.  this sets the string variable to your fields
Email = Me!Email
ref = Me!ref
origin = Me!origin
destination = Me!destination
notes = Me!notes

'***creates an instance of Outlook
Set objOutlook = CreateObject("Outlook.application")
Set objEmail = objOutlook.CreateItem(olMailItem)

'***creates and sends email
With objEmail
    .To = Email
    .Subject = ref & " " & origin & " to " & destination
    .Body = notes
    .Send
    'sends the email in Outlook.  Change to DISPLAY if you want to be able to
      'modify or see what you have created before sending the email
End With

'**closes outlook
objOutlook.Quit
Set objEmail = Nothing

End Function
'****end code****

 

[Godber]

Thanks TerryHogarth21, that is a good bit of code and really easy to understand from my point of view.

The only addition i would like to add is, within the body of the email i want the majority of the email to be generic and then certain points of it filled with customer information.

How would i go about doing this?

Thanks!

Liam

 

[TerryHogarth21]

What you could do is create another string variable I'll call it EmailBody

 Dim EmailBody as string

Then we need to create some generic string while adding in all your other customer variable

 Email Body = "Hello " & CustomerName & ", this is your data that you had requested." & vbCrLf & _
             "" & vbCrLf & _

             " Your contact is " & ContactName & "." & vbCrLf & _
             " If you need anything else please let me know.  Thanks"

Just keep closing the message with " and then add in your string variable in this case CustomerName and ContactName references a string variable that is linked to your form like the above that I had already mentioned.


So that message that I just wrote would look like below

Hello CustomerABC, this is your data that you had requested.

Your contact is ContactABC.

If you need anything else please let me know. Thanks

Then just use that EmailBody in the with part of the code - just be careful with the syntax when you create long strings.
Put it on multiple lines so you can review it for errors if it fails.

With objEmail
     .Body = EmailBody
End with

</pre>

 

[Godber]

TerryHogarth21, you have done it again.

Sorry i didnt reply over the weekend but i simply was not at work.

I have been tweaking this all morning to get this to fit which it now does and what a treat!

I have one more question, please bare with me.

How could i add format to this? for example i would like certain words and strings to be bold and underlined.

I appreciate all your help on this.
Liam<o></o>

 

[TerryHogarth21]

So here is what you need to do if you want to format specific items.

in the with part of the code change it to

With objEmail  
.BodyFormat = olFormatHTML
.HTMLBody = Emailbody
 End with

Now the body will be HTML and you can utilize the HTML tags - look here to see the various types.
HTML Text Formatting

< starts the tag so <b and /b> ends the tag this would bold anything between the tags

http://msdn.microsoft.com/en-us/library/office/aa171418%28v=office.11%29.aspx