Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Nov 2001
    Posts
    224
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Excel macro call Outlook error (Excel 2003)

    Hi All,

    I have an issue when sending an email from an Excel macro, it works fine unless I add the .Attachments option, when I put this is I get any error -2147352567.

    I have check that the file exits in the appropriate location. I am feeding this option with a string variable containing the information.

    Anybody have any clues.

    Regards

    Mike

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Excel macro call Outlook error (Excel 2003)

    Can you post the relevant part of the code, please?

  3. #3
    3 Star Lounger
    Join Date
    Nov 2001
    Posts
    224
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel macro call Outlook error (Excel 2003)

    Hi Hans,

    Sorry for the delay in responding I had to leave the office.

    Here is the code that I am using to send the email
    ************************************************** ************************************************** **********

    Private Sub Module_1_1()

    On Error GoTo ErrorCheck1_1

    Dim objOLook As New Outlook.Application
    Dim objOMail As MailItem

    If intLetterLevel = 1 Then
    strBody = strMsgText1 + strSignature1
    strSubject = strSubject1
    strManagersEmailAddress = ""
    End If

    If intLetterLevel = 2 Then
    strBody = strMsgText2 + strSignature2
    strSubject = strSubject2
    strManagersEmailAddress = ""
    End If

    If intLetterLevel = 3 Then
    strBody = strMsgText3 + strSignature3
    strSubject = strSubject3
    End If

    Set objOLook = New Outlook.Application
    Set objOMail = objOLook.CreateItem(olMailItem)

    With objOMail
    .To = strEmailAddress
    .CC = strManagersEmailAddress
    .Subject = strSubject
    .Body = strBody
    .Attachments = strFilePath1 + strAttachFile
    .Importance = olImportanceHigh
    .Send
    End With

    Set objOMail = Nothing
    Set objOLook = Nothing

    Exit Sub

    ErrorCheck1_1:

    strErrNumber = Err.Number

    strErrSub = "Module_1_1"
    Module_E

    End Sub

    ************************************************** ************************************************** **************************

    If I take out the .Attachment option it works fine.

    Regards

    Mike

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Excel macro call Outlook error (Excel 2003)

    Change the line
    <code>
    .Attachments = strFilePath1 + strAttachFile
    </code>
    to
    <code>
    .Attachments.Add strFilePath1 & strAttachFile
    </code>
    Attachments is a collection, not a string. You can add items to the collection using its Add method.

    (Changing + to & is not essential, but I prefer using & to concatenate strings)

  5. #5
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Excel macro call Outlook error (Excel 2003)

    Hi,
    Hans has already answered your question, but I notice you have both:
    <code>Dim objOLook As New Outlook.Application</code>
    and:
    <code>Set objOLook = New Outlook.Application</code>
    in your code. I suggest you change the first one to:
    <code>Dim objOLook As Outlook.Application</code>
    rather than reinitialising the objOutlook variable.
    FWIW.
    Regards,
    Rory

    Microsoft MVP - Excel

  6. #6
    3 Star Lounger
    Join Date
    Nov 2001
    Posts
    224
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel macro call Outlook error (Excel 2003)

    Thanks Hans & Rory,

    Problem solved.

    Regards

    Mike

Posting Permissions

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