VBA InputBox Automatic Population

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • loweh1
    New Member
    • Apr 2010
    • 1

    VBA InputBox Automatic Population

    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
Working...