Excel workbook to outlook template

Question

Please see attached image below for reference.

I have an excel workbook that i need to input data into every day on the fly. After inputting data i then need to re input the data into an outlook template and send it to clients.

My outlook template contains a basic table as seen in the picture.

What i want to do is after inputting the data into excel, click the button and it will automatically open the outlook template and fill in the data from the excel workbook ready to be sent.

I've been copying and pasting the data in but its starting to get out of hand because several hundred of these emails need to be done each day.

Any suggestions would greatly be appreciated.

As long as the table remains and the font type doesn't look like something from the 80's then it doesn't matter what format the email is in — Exhausted

Tim Williams Tim Williams · Accepted Answer · 2017-08-08T22:57:53

Here's something I use for simple emails - pretty generic but you can tweak as you wish.

Select a row in your data and run the macro. Adjust the HEADER_ROW and NUM_COLS constants to suit your layout.

Sub NotificationMail()

    Const HEADER_ROW As Long = 1 '<< the row with column headers
    Const NUM_COLS As Long = 7   '<< how many columns of data

    Const olMailItem = 0
    Const olFolderInbox = 6

    Dim ol As Object, fldr, ns, msg
    Dim html As String, c As Range, colReq As Long, hdr As Range
    Dim rw As Range

    On Error Resume Next
    Set ol = GetObject(, "outlook.application")
    On Error GoTo 0

    If ol Is Nothing Then
        On Error Resume Next
        Set ol = CreateObject("outlook.application")
        Set ns = ol.GetNamespace("MAPI")
        Set fldr = ns.GetDefaultFolder(olFolderInbox)
        fldr.display
        On Error GoTo 0
    End If

    If ol Is Nothing Then
        MsgBox "Couldn't start Outlook to compose mail!", vbExclamation
        Exit Sub
    End If

    Set msg = ol.CreateItem(olMailItem)

    Set rw = Selection.Cells(1).EntireRow

    msg.Subject = "Here's your information"

    html = "<style type='text/css'>"
    html = html & "body, p {font:10pt calibri;padding:40px;}"
    html = html & "table {border-collapse:collapse}"
    html = html & "td {border:1px solid #000;padding:4px;}"
    html = html & "</style>"

    html = html & "<p>Your request has been updated:</p>"
    html = html & "<table>"


    For Each c In rw.Cells(1).Resize(1, NUM_COLS).Cells
        If c.Column <> 4 Then '<<< EDIT to exclude ColD
            Set hdr = rw.Parent.Cells(HEADER_ROW, c.Column) '<< get the header text for this cell

            html = html & "<tr><td style='background-color:#DDD;width:200px;'>" & _
               hdr.Value & _
               "</td><td style='width:400px;'>" & Trim(c.Value) & "</td></tr>"
        End If 'we want this cell
    Next c

    html = html & "</table>"

    msg.htmlbody = html
    msg.display

End Sub

Excel workbook to outlook template

2 Answers