Results 1 to 2 of 2
  1. #1
    3 Star Lounger
    Join Date
    Aug 2002
    Thanked 0 Times in 0 Posts

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


    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 <img src=/S/bow.gif border=0 alt=bow width=15 height=15> <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23>

    PS: Wish there were a knob on the TV to turn up the intelligence. There's a knob called "brightness," but that doesn't work

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Weert, Limburg, Netherlands
    Thanked 0 Times in 0 Posts

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

    This code creates an email message in Outlook:

    <pre>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
    Set oOLapp = Nothing
    Set oMailItem = Nothing
    End With
    CreateMail = True
    Exit Function
    CreateMail = False
    Exit Function
    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

    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    Professional Office Developers Association

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts