asked on

Refer to a query value in html of email

Where the below code says MANAGERNAME on the first line. I need it to bring up a value on a query. The query is only a one row query based on a value in the. The query name is qryOfficeInfoMainForm. The Column is PersonInCharge which is the 3 column over. Is it possible to add that where it says MANAGERNAME?

Private Sub EmailInvoice_Click()
Dim sSubj As String, sBody As String
sSubj = "We are all set for swim lessons!"

sBody = "<HTML> " & XXXXXX & ", <BR>" _
    & "<P> We are excited to start swim lessons with you this summer! Your manager's name is MANAGERNAME. Swim lessons brings excitement, anxiety, fun, fear, and nerves all wrapped up in one. We are thrilled you choose us to guide you through the adventure. We will strive to exceed your expectations in every way. Attached you will see a confirmation of the first package of lessons as we discussed on the phone.</P>" _
    & "<P> XXXX is your account manager who will guide you through your swim lessons experience. XXX is excited to help you with any schedule issues, questions, concerns or even if you just want to chat. The direct line to meet XXXX is XXX-XXX-XXXX. XXX will be giving you a call for an introduction in the near future. </P>" _
    & "<P> Visit us on social media! Great place to get to know us and post some of your own. Interested in an easy way to get two free days of swim lessons? Earn an extra swim lesson for each of the below:<P>" _
    & "<UL><LI>6 Posts to Facebook or Instagram: Make one post for everyday of swim lessons and you will receive one free lesson! Make sure to use #XXXXX and tag us @XXXXXX.</LI>" _
    & "<LI>Refer 5 Friends:  Simply refer 5 friends for swim lessons and receive one free lesson. Fast and easy! Click this link for referral sheet: XXXXXXXXXXXXXXXXXX</LI></UL></P>" _
    & "<P>We would like to thank you again for choosing XXXXX to guide you through your swim lesson adventure. We have been successfully helping families just like yours since the summer of 2000. You are in good hands! <P>" _
   & "Have a great summer day,<BR><BR> <BR>Big Arms Swimmers Team<BR>" _
   & "XXX-XXX-XXXX<BR> <BR>" _
    & "<a href=http://www.XXXXXXX.com>www.XXXXX.com</a><BR>" _
     & "<a href=http://www.Facebook.com>www.Facebook.com/XXXXX</a>. </HTML>"

DoCmd.OpenReport "CustomerInvoicesIndividual", acViewPreview, , , acHidden
DoCmd.OutputTo acOutputReport, "CustomerInvoicesIndividual", acFormatPDF, "C:\Invoices\CustomerInvoices.pdf", True
DoCmd.Close acReport, "CustomerInvoicesIndividual"

Set objOutlook = CreateObject("Outlook.Application")
        Set objEmailMessage = objOutlook.CreateItem(olMailItem)
        objEmailMessage.To = Me.Parent.Parent.[Email]
        objEmailMessage.Subject = sSubj
        objEmailMessage.htmlBody = sBody & vbCrLf & vbCrLf
        objEmailMessage.Display
        objEmailMessage.Attachments.Add "C:\Invoices\CustomerInvoices.pdf"


End Sub

Open in new window

ASKER CERTIFIED SOLUTION

Gozreh

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

cansevin

ASKER

Thanks!