Create a Dynamic Outlook Email using Access VBA

Question

Here is my current setup...

I have an Access project tracking database with project description values (example: Project_ID) and I have a button "Create Email" which I've successfully gotten to open Outlook, pick an Outlook draft, and automatically generate the appropriate subject line.

However, I have no clue where to start on this next step. Any help is appreciated!

I would like the Outlook email to have dynamic fields that are linked to the fields in my Access database. I'm not even sure if I should be trying to code in Access or in Outlook for this step!

Example of what I want:

NAME,

Attached are your documents for:

Project_ID Project_Name Project_Type <--Values from my Access database

Sincerely,

my signature

Answer 1

you should code in MS Access. a pseudo code would be:

1. Read all values from your access table
2. create outlook object, mail object
3. Open outlook mail and add subject, content, attachments
4. send or leave it to the employee to send

call this like

send_email_message "to@to.to","","","Email subject","Add your email content/body like project id= &field1, project name = field2 and so on"

you can attach more than one file just join the document name with a ";" delimiter. like

"C;\file1.txt;c:\File2.txt"

here a sending email function: this function uses outlook objects (import them in reference) or change it to late binding by vba.createobject("outlook.application")

Function SEND_EMAIL_MESSAGE(mTo As String, mCC As String, mBC As String, mSubject As String, mBody As String, Optional useOwnSignature As Boolean = False, Optional DisplayMsg As Boolean = False, Optional isHTML As Boolean = False, Optional AttachmentPath = "") As Boolean
'---------------------------------------------------------------------------------------
' Procedure : SEND_EMAIL_MESSAGE
' Author    : KRISH KM
' Date      : 01/09/2013
' Purpose   : Send emails using outlook
'---------------------------------------------------------------------------------------
'
' Please check the reference for Microsoft Outlook 14.0 object library for outlook 2010.

    Dim oAPP As Outlook.Application
    Dim oMail As Outlook.MailItem
    Dim oAPPAttach As Outlook.Attachment
    Dim mSignature As String

    On Error GoTo ERROR_EMAIL
    ' Create the Outlook session.
    Set oAPP = New Outlook.Application

    ' Create the message.
    Set oMail = oAPP.CreateItem(olMailItem)

    With oMail
        ' Add the To recipient(s) to the message.
        .to = mTo
        .cc = mCC
        .BCC = mBC
        .Subject = mSubject

        If useOwnSignature Then .BodyFormat = olFormatHTML
        .Display

        If useOwnSignature Then
            If isHTML Then

                mSignature = .HTMLBody
                .HTMLBody = mBody & mSignature
            Else
                mSignature = .body
                .body = mBody & mSignature
            End If
        Else
            .body = mBody
        End If

        ' Add attachments to the message.
        If Not IsMissing(AttachmentPath) Then
            Dim mFiles() As String
            If (VBA.Right(AttachmentPath, 1)) <> ";" Then AttachmentPath = AttachmentPath & ";"
            mFiles = VBA.Split(AttachmentPath, ";")
            Dim i As Integer
            For i = 0 To UBound(mFiles) - 1
                If Not mFiles(i) = "" Then .Attachments.Add (mFiles(i)) 'Set oAPPAttach = .Attachments.Add(mFiles(i))
            Next i

        End If

        ' Should we display the message before sending?
        If DisplayMsg Then
            .Display
        Else
            .Send
        End If
    End With

    SEND_EMAIL_MESSAGE = True
EXIT_ROUTINE:
    On Error GoTo 0
    Set oAPP = Nothing
    Set oMail = Nothing
    Exit Function

ERROR_EMAIL:
    SEND_EMAIL_MESSAGE = False
    GoTo EXIT_ROUTINE
End Function