Unanswered: Code to email file to single record from query?
I put together a macro that runs 4 codes. The first code creates a PDF copy of a coverletter. The second code creates a PDF copy of the invoices to accompany the coverletter. The third code merges the two PDFs into one. The fourth code emails the merged file to a single specified recipient. All of these steps work very well but I need to change the fourth code so that it pulls the email address from a query within my database. That query will only return one record and email address at any given time. This is probably very easy for someone who knows VB well but unfortunatly, I'm still learning and nothing I tried was working. Here is the code I am currently running in the fourth part of the macro, when I pull up the function under RunCode, I specify whatever email address manually by typing it in the "ToEmail" prompt:
Public Function SendEMail(ToEmail As String) As Boolean
' Must have Tools>Referrence Microsoft DAO 3.X Object Library
' Microsoft Oulook X.0 Object Model, and Microsoft Scripting
' Runtime checked.
Dim db As DAO.Database
Dim MyOutlook As Outlook.Application
Dim MyMail As Outlook.MailItem
Dim Subjectline As String
Dim BodyFile As String
Dim fso As FileSystemObject
Dim MyBody As TextStream
Dim MyBodyText As String
Set fso = New FileSystemObject
Set db = CurrentDb()
Set MyOutlook = New Outlook.Application
Set MyMail = MyOutlook.CreateItem(olMailItem)
MyMail.To = ToEmail
MyMail.Subject = ""
MyMail.Body = ""
MyMail.Attachments.Add "H:\Access\Dunning Letters\1DL.pdf", olByValue, 1, "My Displayname"