Sending a single Email to a single record in a query

[Kregg]

I am trying to create a form with a button attached to each record that would allow the user to click the button and it would automatically open outlook and fill in the TO:, SUBJECT: and BODY: fields. I am very close to this but I am running in to a few issues.

Here is the code that I currently have:

Private Sub Command33_Click()
Dim strEmail As String
Dim strMsg As String
Dim oLook As Object
Dim oMail As Object
Set oLook = CreateObject("Outlook.Application")
Set oMail = oLook.CreateItem(0)
With oMail
    .To = E_MAIL
    .HTMLBody = [EMAIL_BODY]
    .Subject = [EMAIL_SUBJECT]
    .Display
 
End With
doEmailOutlookErrExit:
    Exit Sub
doEmailOutlookErr:
    MsgBox Err.Description, vbOKOnly, Err.Source & ":" & Err.Number
    Resume doEmailOutlookErrExit
End Sub

There are two issues I keep running into:

1. This code opens outlook and populates all of the fields but pastes the email incorrectly. Instead of pasting just the email (email@email.com) it pastes the html tags as well (email@email.com#mailto:email@email.com#) which means that the user would have to delete everything between the #'s in order to send the email every time.

2. I currently have the email BODY pulling from a table but this obviously limits what I can do. I would like to simply encode the BODY within the VBA code. The setup I am looking for is:
one paragraph
a blank line
a hyperlink to a website
a blank line
another paragraph

Any help on these two issues would be greatly appreciated as I am losing my mind trying to find an answer on my own. Thanks!

:banghead:

 

[pbaldy]

1) Sounds like you're using a hyperlink data type; I use a plain text field.

2) I'd think having it in code is more limiting, but:

"blah blah" & vbCrLf & vbCrLf & "hyperlink" & vbCrLf & vbCrLf & "and so on"

 

[Kregg]

1) Sounds like you're using a hyperlink data type; I use a plain text field.

I do use hyperlink data type...this I in place because the users like the ability to click the email in order to send an email from other forms that does not have a specific message attached.

2) I'd think having it in code is more limiting, but:

"blah blah" & vbCrLf & vbCrLf & "hyperlink" & vbCrLf & vbCrLf & "and so on"

If I simply type www.website.com where you have "hyperlink" will this automatically create a hyperlink in outlook?

 

[pbaldy]

I do use hyperlink data type...this I in place because the users like the ability to click the email in order to send an email from other forms that does not have a specific message attached.

True, but I don't like how it adds extra characters. I simply code what I want to happen when they click on the email.

If I simply type www.website.com where you have "hyperlink" will this automatically create a hyperlink in outlook?

I haven't tested, but since you're using the HTML body it would be easy enough to include the tags if it doesn't.

 

[Kregg]

Is there s simple way to convert hyperlinked emails to text? My fear is that if I do this they will convert but will include the unwanted #s. Is this true or am I overthinking it?

 

[pbaldy]

I've never tried, as I've never used the data type. Make a copy of the db and experiment with it. If it leaves them, it looks like it would be easy enough to run an update that only grabs the text before the first #.

 

[Kregg]

Thanks pbaldy!!! so far everything is working great. I am only having issues with getting the break line between the paragraph, hyperlink, and paragraph. The code only seems to add a space but not add a "return" in order to create a blank line of text. Any suggestions?

 

[pbaldy]

Oh sorry, with HTML body you want to include <br> inside the string instead of the vbCrLf outside. Using the original example:

"blah blah<br><br>" & "hyperlink" & "<br><br> and so on"

 

[Kregg]

You are a life saver!!!! Everything works like a charm!

 

[pbaldy]

Happy to help!