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.Applica tion
Dim MyMail As Outlook.MailIte m
Dim Subjectline As String
Dim BodyFile As String
Dim fso As FileSystemObjec t
Dim MyBody As TextStream
Dim MyBodyText As String
Dim Contact As String
Dim qryMail As QueryDef
'offer for user inut
Contact$ = InputBox$("Cont act?")
'Use the query to select data for chosen Name
Set qryMail = CurrentDb.Query Defs("Query1")
qryMail.Paramet ers(0) = Contact$
'Call Outlook
Set MailList = qryMail.OpenRec ordset
Set fso = New FileSystemObjec t
Set MyOutlook = New Outlook.Applica tion
Set db = CurrentDb()
'Collate the email basic information
Set MyMail = MyOutlook.Creat eItem(olMailIte m)
MyMail.To = MailList("Email 1")
MyMail.Subject = MailList("Subje ct")
MyMail.Body = MailList("Openi ngSalutation") & vbNewLine & vbNewLine & MailList("Parag raph1") & vbNewLine & vbNewLine & MailList("Parag raph2") & vbNewLine & vbNewLine & MailList("Parag raph3") & vbNewLine & vbNewLine & MailList("Parag raph4") & vbNewLine & vbNewLine & MailList("Closi ngSalutation") & vbNewLine & vbNewLine & MailList("Name" )
'Export the attachment
DoCmd.RunSavedI mportExport ("Export-FORM")
MsgBox Contact$, vbDefaultButton 1
'Import the attachment onto email
MyMail.Attachme nts.Add "C:\Users\Hazel \Documents\FORM .rtf", olByValue, 1, "My Displayname"
MyMail.Display
End Function
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.Applica tion
Dim MyMail As Outlook.MailIte m
Dim Subjectline As String
Dim BodyFile As String
Dim fso As FileSystemObjec t
Dim MyBody As TextStream
Dim MyBodyText As String
Dim Contact As String
Dim qryMail As QueryDef
'offer for user inut
Contact$ = InputBox$("Cont act?")
'Use the query to select data for chosen Name
Set qryMail = CurrentDb.Query Defs("Query1")
qryMail.Paramet ers(0) = Contact$
'Call Outlook
Set MailList = qryMail.OpenRec ordset
Set fso = New FileSystemObjec t
Set MyOutlook = New Outlook.Applica tion
Set db = CurrentDb()
'Collate the email basic information
Set MyMail = MyOutlook.Creat eItem(olMailIte m)
MyMail.To = MailList("Email 1")
MyMail.Subject = MailList("Subje ct")
MyMail.Body = MailList("Openi ngSalutation") & vbNewLine & vbNewLine & MailList("Parag raph1") & vbNewLine & vbNewLine & MailList("Parag raph2") & vbNewLine & vbNewLine & MailList("Parag raph3") & vbNewLine & vbNewLine & MailList("Parag raph4") & vbNewLine & vbNewLine & MailList("Closi ngSalutation") & vbNewLine & vbNewLine & MailList("Name" )
'Export the attachment
DoCmd.RunSavedI mportExport ("Export-FORM")
MsgBox Contact$, vbDefaultButton 1
'Import the attachment onto email
MyMail.Attachme nts.Add "C:\Users\Hazel \Documents\FORM .rtf", olByValue, 1, "My Displayname"
MyMail.Display
End Function