Automation, Excel 2 Outlook (Win XP / Office 2003 UK)

Topic

New Reply

Hi,

Got an idea, but before I set out on a wild goose chase, I would appreciate some opinions and guidelines on this – as I’m a newbie to programming with Outlook.

My idea is to have my values in Excel and with the press of a button to build and populate a standard form in outlook, that will serve as a dynamically build mail template.

1. I already have some labels and resulting fields in Excel (extracts from a database Col A is labels, col B result fields)
2. Would like those in a mail in outlook (Concatenating labels and result fields on a form or the like (ie. A1 & B1, A2 & B2 etc. – like “Name: John”)
3. A few fields in the final mail must be filled in by the user, so ‘subject’ and a ‘text box’ should be for free edit

Does anyone have a sample or description of hoow I can achieve this??

This would indeed be a nice Xmas present

Reply | Quote

Replies

This code creates an email message in Outlook:

Private Function CreateMail(sTo As String, sSubject As String, sBody As String, _
                                                   sAttachment As String) As Boolean
    Dim oMailItem As Object
    Dim oOLapp As Object
    On Error Resume Next
    'Fire up Outlook
    Set oOLapp = GetObject(, "Outlook.application")
    If Err.Number > 0 Then
        On Error GoTo LocErr
        Set oOLapp = CreateObject("Outlook.application")
    End If
    On Error GoTo LocErr
    'Open email object
    Set oMailItem = oOLapp.CreateItem(0)
    With oMailItem
        .To = sTo
        .Subject = sSubject
        .body = sBody
        If Len(sAttachment) > 0 Then
            .attachments.Add sAttachment
        End If
        'Display the message so user can edit and decide whether or not to send
        .Display
        Set oOLapp = Nothing
        Set oMailItem = Nothing
    End With
    CreateMail = True
    Exit Function
TidyUp:
    CreateMail = False
    Exit Function
LocErr:
    If Err.Number = 429 Then
        MsgBox "Outlook did not start, pleas eopen Outlook and try again.", vbExclamation + vbOKOnly, _
                        "Outlook is niet gestart"
        Resume TidyUp
    End If

    MsgBox "Error during creation of email. Message:" & vbNewLine & _
           Err.Description, vbOKOnly + vbExclamation, "Error during creating email"

    Resume TidyUp
End Function

Reply | Quote