Email to query result with varied email body

[hlkelly]

Hi.

I used this forum to create this code in order to send an e-mail confirmation to clients who have placed orders. My goal is to send a bulk e-mail to all of those who appear in the query results. I created a form which displays each record individually but I really want the e-mail to be sent to everyone in the query result. Nonetheless, this is the code I am using behind the button on the form. I get a "compile error: user-defined type not identified" error but when I try to set the reference (>tools >references) "references" is greyed out.

Private Sub Command28_Click()
'******begin code******
Dim email, ref, origin, destination, notes As String
Dim objOutlook As Outlook.Application
Dim objEmail As Outlook.MailItem

email = Me!ContactEmail
OrderID = Me!OrderID
TrackingNumber = Me!TrackingNumber

'***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 & " " & destination
.Body = notes
.Send
End With

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

Exit Sub
'****end code****

End Sub


#1 I need help with the error message
#2 Am I going about this the right way? Each e-mail will have a different body. Should I export the data to a fixed location on the server and have my users simply do a mail merge?

I would like as few steps as possible...one click of a button is always best for them.

Thanks.

Heather

 

[hlkelly]

I am still working on this. Here is the updated code to better reflect my db fields. (Thanks to Nathan for his tips!!)

Private Sub Command28_Click()
'******begin code******
Dim email, strbody As String
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!ContactEmail
OrderID = Me!OrderID
TrackingNumber = Me!TrackingNumber
CompanyName = Me!CompanyName
ShipAddress = Me!ShipAddress
ShipAddress2 = Me!ShipAddress2
ShipCity = Me!ShipCity
ShipState = Me!ShipState
ShipZip = Me!ShipZip


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

strEmail = txtEmail
strbody = strbody & "Thank you for your order. Please find shipping and tracking information below." & Chr(13) & Chr(13) & Chr(13)
strbody = strbody & "Order #: " & OrderID & Chr(13)
strbody = strbody & "Tracking Number(s): " & TrackingNumber & Chr(13)
strbody = strbody & "Track your order here:

http://www.fedex.com/Tracking?cntry_code=us&link=4

" & Chr(13)
strbody = strbody & "If you have any questions, please contact our office: 888.555.1212," & Chr(13) & Chr(13)
strbody = strbody & "MY COMPANY NAME HERE"


'***creates and sends email
With objEmail
.To = ContactEmail
.Subject = "Your order information from MY COMPANY NAME HERE"
.Body = notes
.Send
End With

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

Exit Sub
'****end code****

End Sub