I'm using an Access form to populate an Outlook email template. I have been successful getting the data from the form into an email using this sequence Body = Replace(Body, "@1", Me.[BUREAU CODE]) with a control source tied to a specific table or query. My problem is one of my form fields uses =dlookup to populate the form with the person's email address based on various criteria. Because of that, I don’t know how to tell Access to take that field and put it in the email To field, since I don't have a specific Access field name like [Bureau Code] to use in my "Replace" sequence.
Do you mean that the Field on the form is unbound in other words it's not linked to an underlying table? If that's the case then you could probably Direct your VBA code to the actual text box on the form something similar to this method:- https://youtu.be/zAED0N31sm0?t=4m41s
I sent a couple of posts recently where I tried including screenshot images as part of the post.. both were sent for moderation. Neither have since appeared.
I'm not sure if those are the ones you both mentioned
Either way, whilst I totally understand the need for moderation, I'm not clear why my messages were lost...
In the meantime, I've given up on 'inline' images in posts
No need to replace for those items.
The message has them as properties?
Below is how I send an email from Access in my charity database.
I actually use the names in my outlook contacts rather than hard coding them. That way if a caseworker changes their email (and they have) it still works?
Code:
' Create the message if first time we are in a different client or tran type.
Set objOutlookMsg = objOutlook.CreateItemFromTemplate(strTemplatePath & "\SSAFA Email.oft")
With objOutlookMsg
' Set the category
.Categories = "SSAFA"
.Importance = olImportanceHigh
' Add the To recipient(s) to the message.
Set objOutlookRecip = .Recipients.Add("Jim Needs - SSAFA Swansea")
objOutlookRecip.Type = olTo
' Add the CC recipient(s) to the message.
If rs!CCOffice Then
Set objOutlookRecip = .Recipients.Add("** SSAFA West Glamorgan Branch")
objOutlookRecip.Type = olCC
End If
' Need to get the Case Worker name from table'
If rs!CaseWorker > 0 Then
rsCW.FindFirst "[ID] = " & rs!CaseWorker
strCaseWorker = rsCW!Data
Else
strCaseWorker = ""
End If
If strCaseWorker <> "" Then
Set objOutlookRecip = .Recipients.Add(strCaseWorker)
objOutlookRecip.Type = olCC
End If
' Set the Format, Subject, Body, and Importance of the message.
'.BodyFormat = olFormatHTML
strClient = rs!Client
If strType = "Payment" Then
.Subject = " Payment Made - " & strClient
Else
.Subject = "Deposit Received - " & strClient
End If
' Now start the email with header
'iColon = InStr(strClient, ":")
' If iColon = 0 Then iColon = Len(strClient) + 1
.HTMLBody = strHeader & "<table><tr>"
' .HTMLBody = .HTMLBody & "<td>" & "Client: " & strPadCol & Left(strClient, iColon - 1) & strEndPad
'End If
End With
I'm using an Access form to populate an Outlook email template. I have been successful getting the data from the form into an email using this sequence Body = Replace(Body, "@1", Me.[BUREAU CODE]) with a control source tied to a specific table or query. My problem is one of my form fields uses =dlookup to populate the form with the person's email address based on various criteria. Because of that, I don’t know how to tell Access to take that field and put it in the email To field, since I don't have a specific Access field name like [Bureau Code] to use in my "Replace" sequence.
I sent a couple of posts recently where I tried including screenshot images as part of the post.. both were sent for moderation. Neither have since appeared.
I'm not sure if those are the ones you both mentioned
Either way, whilst I totally understand the need for moderation, I'm not clear why my messages were lost...
In the meantime, I've given up on 'inline' images in posts
They were in the code repository, which is a moderated forum. I've been out of town, though another moderator should have seen and dealt with them. I've moved them to an unmoderated forum:
You have enough posts that they should not be moderated anymore. Those last 2 got moderated because of the forum you put them in. I think anything posted to a forum in the "Microsoft Access Reference" section requires moderation. Just post to one of the regular forums and you should be fine.
Gasman: I appreciate your response! I’m not sure it quite meets my problem either that or I may be misunderstanding your code. I’m using DLookUp to find my email address Email1 from the table Contacts, based on matching the Allotment Code on the form to the Allotment number in the Contacts table. This is what I am using to look up the individual’s email address to enter it on the form: =DLookUp("Email1","[Contacts]","[Allotment]=" & [Allotment Code]). My problem is I can’t figure out what I need to write to get the email address into the "To" field in my email.
Note: I have brackets around Email1, but the system wouldn't let me post it thinking it was an email address
Unless I am misunderstanding you.
The message has properties much like a control has.
So in the code I posted I set the .To of the email from a hard coded value "Jim Needs". That is just so it searches my outlook address book and gets the current email address.
You just need to put your variables in the properties of the message.
You are already doing it with .Body ?
So the code would be something along the lines of
Code:
Set objOutlookRecip = .Recipients.Add(Me.EmailAddress)
objOutlookRecip.Type = olTo
Where Me.EmailAddress is the control on your form?
Thst is just one syntax.
In another DB I am doing as below, which is perhaps eaiser to read. It is just that most of my code is gleaned through googling and modifying, or being shown a method here.
Code:
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
On Error Resume Next
' Change the mail address and subject in the macro before you run it.
With OutMail
.Display
End With
With OutMail
.To = pStrToNames
.cc = "yyy@xxxx.co.uk"
.BCC = ""
.Subject = pstrSubject
.htmlbody = Replace(.htmlbody, strDiv, strDiv & strBody)
'.attachments.Add pstrFilename & ".pdf"
.attachments.Add pstrFilename
.Send
End With
The reason they are different is the first was created at home, and the second in work. As I did not have the code to hand in work I would have googled again for a method, and that just happened to be it.?
