I have a function which produces an email for a specific contact, and I have a report which again is for the same specific contact. This function opens Outlook and enters the email data into the message body (derived from one Query), and it then attaches the report (derived from another query) to the email.
This routine works perfectly, however, it means I have to enter the Contact name into both InputBoxes. Is there a way to use the input from the first InputBox to populate the second?
Here is my code:
Option Compare Database
Option Explicit
Public Function SendEMail()
'define the variables
Dim db As DAO.Database
Dim MailList As DAO.Recordset
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
Dim Contact As String
Dim qryMail As QueryDef
'offer for user inut
Contact$ = InputBox$("Contact?")
'Use the query to select data for chosen Name
Set qryMail = CurrentDb.QueryDefs("Query1")
qryMail.Parameters(0) = Contact$
'Call Outlook
Set MailList = qryMail.OpenRecordset
Set fso = New FileSystemObject
Set MyOutlook = New Outlook.Application
Set db = CurrentDb()
'Collate the email basic information
Set MyMail = MyOutlook.CreateItem(olMailItem)
MyMail.To = MailList("Email1")
MyMail.Subject = MailList("Subject")
MyMail.Body = MailList("OpeningSalutation") & vbNewLine & vbNewLine & MailList("Paragraph1") & vbNewLine & vbNewLine & MailList("Paragraph2") & vbNewLine & vbNewLine & MailList("Paragraph3") & vbNewLine & vbNewLine & MailList("Paragraph4") & vbNewLine & vbNewLine & MailList("ClosingSalutation") & vbNewLine & vbNewLine & MailList("Name")
'Export the attachment
DoCmd.RunSavedImportExport ("Export-FORM")
MsgBox Contact$, vbDefaultButton1
'Import the attachment onto email
MyMail.Attachments.Add "C:\Users\Hazel\Documents\FORM.rtf", olByValue, 1, "My Displayname"
MyMail.Display
End Function