Consulting

Results 1 to 16 of 16

Thread: Required to add attachment to .Send

  1. #1
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location

    Required to add attachment to .Send

    Driving me NUTS


    In the simple little macro below, if I do NOT include an attachment, I get an error 287 on .Send

    With an attached file, the message is generated and sent as expected

    Is there any way that I would not have to .Add an attachment

    Win7 64bit, MS Office 2010

    Thanks

    Option Explicit
    Sub SendEmail()
        Dim OutApp As Object
        Dim OutMail As Object
        
        Dim sFile As String, sBody As String, sRecipiant
        
        sBody = "This is an email message"
        sFile = "C:\Users\Me\Scripts\Logs\bu2000-2015-01-20.log"
        sRecipiant = "me@here.net"
        Set OutApp = CreateObject("Outlook.Application")
        Set OutMail = OutApp.CreateItem(0)
        With OutMail
            .To = sRecipiant
            .CC = vbNullString
            .BCC = vbNullString
            .Subject = "email test -- " & Format(Now, "General Date")
            .Body = sBody
            'without attachment I get an error 287 on the .Send
            .Attachments.Add sFile
            .ReadReceiptRequested = False
            'err 287 - application-defined or object-defined error
            .Send
        End With
        Set OutMail = Nothing
        Set OutApp = Nothing
            
    End Sub
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  2. #2
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    With OutMail
    If Len(sFile) >0 Then .Attachments.Add sFile
    End With
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  3. #3
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    Sorry - I wasn't clear

    If I comment out the line that adds an attachment I get the error.

    '     .Attachments.Add sFile

    So unless I have something attached to the mail item, I get the error. So the intuitive thought would be a simple little macro like this would send a email (text only)


    Option Explicit
    Sub SendEmail()
        Dim OutApp As Object
        Dim OutMail As Object
        
        Dim sBody As String, sRecipiant as string
        
        sBody = "This is an email message"
        sRecipiant = "me@here.net"
        Set OutApp = CreateObject("Outlook.Application")
        Set OutMail = OutApp.CreateItem(0)
        With OutMail
            .To = sRecipiant
            .CC = vbNullString
            .BCC = vbNullString
            .Subject = "email test -- " & Format(Now, "General Date")
            .Body = sBody
            .ReadReceiptRequested = False
            .Send
        End With
        Set OutMail = Nothing
        Set OutApp = Nothing
            
    End Sub
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  4. #4
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    Option Explicit
    Sub SendEmail()
        Dim OutApp As Object
        Dim OutMail As Object
        
        Dim sBody As String, sRecipiant as string
        
        sBody = "This is an email message"
        sRecipiant = "me@here.net"
        Set OutApp = CreateObject("Outlook.Application")
        Set OutMail = OutApp.CreateItem(0)
        With OutMail
            .To = sRecipiant
            .CC = vbNullString
            .BCC = vbNullString
            .Subject = "email test -- " & Format(Now, "General Date")
            .Body = sBody
            .ReadReceiptRequested = False
            .Send
        End With
        Set OutMail = Nothing
        Set OutApp = Nothing
            
    End Sub
    Are you saying that raises a 287 Error?
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  5. #5
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    Yes, if I comment out the .Attachments.Add line, the .Send generates an error

    When I add any attachment, email functions normally (or at least the mail and the attachment get sent
    Attached Images Attached Images
    Last edited by Paul_Hossler; 01-20-2015 at 04:55 PM.
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  6. #6
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    OK, I think I have it ... finally

    I don't understand the reason to add the .GetNamespace and a few other lines, but at least I am not forced to always include a dummy attachment. Found the hint at another forum

    http://www.experts-exchange.com/Soft....html#view-all


    Option Explicit
    
     
    Sub SendEmail3()
        Const olFolderInbox As Long = 6
        
        Dim oApp As Object, oMail As Object, oFld As Object
        
        Dim sFile As String, sBody As String, sRecipiant
        
        sBody = "This is an email message"
        sRecipiant = "abc@def.net"
        Set oApp = CreateObject("Outlook.Application")
        Set oFld = oApp.GetNamespace("MAPI").GetDefaultFolder(olFolderInbox)
        Set oMail = oFld.Items.Add
        
        sFile = vbNullString
        With oMail
            .To = sRecipiant
            .CC = vbNullString
            .Subject = "email test 3 -- " & Format(Now, "General Date")
            .Body = sBody
            If Len(sFile) > 0 Then .Attachments.Add sFile
            .send
        End With
    
        sFile = "C:\Users\ABC\Scripts\Logs\bu2000-2015-01-20.log"
        Set oMail = oFld.Items.Add
        With oMail
            .To = sRecipiant
            .CC = vbNullString
            .Subject = "email test 3 with attachment-- " & Format(Now, "General Date")
            .Body = sBody
            If Len(sFile) > 0 Then .Attachments.Add sFile
            .send
        End With
    
        Set oMail = Nothing
        Set oFld = Nothing
        Set oApp = Nothing
            
    End Sub

    (and I thought PowerPoint was confusing - doesn't even hold a candle to Outlook)
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  7. #7
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    Is it possible that this is a Microsoft Update caused Error?

    Sub SendEmail()
        Dim OutApp As Object
        Dim OutMail As Object
        
        Dim sBody As String, sRecipiant as string
        
        sBody = "This is an email message"
        sRecipiant = "me@here.net"
        Set OutApp = CreateObject("Outlook.Application")
        Set OutMail = OutApp.CreateItem(0)
        With OutMail
            .To = sRecipiant
            .CC = vbNullString
            .BCC = vbNullString
            .Subject = "email test -- " & Format(Now, "General Date")
            .Body = sBody
            .ReadReceiptRequested = False
            '.Send
    'with and without attachments.Add
    Debug.PRint(.Attachments.Count)
        End With
    
        Set OutMail = Nothing
        Set OutApp = Nothing
            
    End Sub
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  8. #8
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    Outlook 2002 help says

    To ensure consistent results, always save an item before adding or removing objects in the Attachments collection of the item.

    The following Visual Basic for Applications example creates a new mail message, attaches a Q496.xls as an attachment (not a link), and gives the attachment a descriptive caption.
    Set myOlApp = CreateObject("Outlook.Application")
    Set myItem = myOlApp.CreateItem(olMailItem)
    myItem.Save
    Set myAttachments = myItem.Attachments
    myAttachments.Add "C:\My Documents\Q496.xls", _
        olByValue, 1, "4th Quarter 1996 Results Chart"
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  9. #9
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    Is it possible that this is a Microsoft Update caused Error?
    More likely a Paul caused error by not understanding the Outlook object model.

    The Outlook object model is very confusing to me and I tend to just 'cookbook' the few tasks where I need to interface with Outlook
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  10. #10
    If I can interject a variation on a theme, the following will create a message, with or without a named attachment (if the attachment file exists) and it uses the Outlook editor to edit the message body, in this case for no better reason than it retains the default signature, though you can use it to format the range as required.
    Option Explicit
    Sub SendMessageWithAttachment()
        SendEmail "c:\path\example.txt" 'the attachment is optional
    End Sub
    
    Sub SendEmail(Optional sFile As String)
    Dim OutApp As Object
    Dim OutMail As Object
    Dim olInsp As Object
    Dim wdDoc As Object
    Dim oRng As Object
    Dim sBody As String
    Dim sRecipient As String
        sBody = "This is an email message"
        sRecipient = "me@here.net"
        Set OutApp = CreateObject("Outlook.Application")
        Set OutMail = OutApp.CreateItem(0)
        With OutMail
            .To = sRecipient
            .Subject = "email test -- " & Format(Now, "General Date")
            .BodyFormat = 2
            Set olInsp = .GetInspector
            Set wdDoc = olInsp.WordEditor
            Set oRng = wdDoc.Range(0, 0)
            oRng.Text = sBody
            If FileExists(sFile) Then
                .Attachments.Add sFile
            End If
            .ReadReceiptRequested = False
            .Display 'This line is required
            '.sEnd 'This line can be restored after testing
        End With
        Set OutMail = Nothing
        Set OutApp = Nothing
    lbl_Exit:
        Exit Sub
    End Sub
    
    Private Function FileExists(filespec) As Boolean
    Dim fso As Object
        Set fso = CreateObject("Scripting.FileSystemObject")
        If fso.FileExists(filespec) Then
            FileExists = True
        Else
            FileExists = False
        End If
    lbl_Exit:
        Exit Function
    End Function
    Graham Mayor - MS MVP (Word) 2002-2019
    Visit my web site for more programming tips and ready made processes
    http://www.gmayor.com

  11. #11
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    Graham -- thanks,

    In the particular case I was trying to include the email sending with a non-interactive task so I wanted to to try it w/o the .Display

    However, I get an Object not set on the Set oRng = line because wdDoc is Nothing
    Attached Images Attached Images
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  12. #12
    1. Are you running the macro from Outlook itself? Though the same code works here from Excel and Word also.
    2. Which Office version? Your header suggests 2010 64 bit. though you also mention Office 2002. I don't think I included anything incompatible with the 64 bit version.
    3. .Display is essential to be able to view the message. If you add .Send the message only briefly flashes on screen. I left that commented out so that you could check the message. However, with this particular macro, the .Display option is not required. The message will still be produced.
    Graham Mayor - MS MVP (Word) 2002-2019
    Visit my web site for more programming tips and ready made processes
    http://www.gmayor.com

  13. #13
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    1. Running under Excel 2010 (32 bit MS Office) using 64 bit Win7
    2. I think SamT mentioned 2002
    3. I intend to port the code to a VBS script to send completion email out after nightly backup so it would be going direct to the .Send w/o the .Display.

    However, I still have wdDoc = Nothing using VBA in Excel

    Thoughts?
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  14. #14
    1. I just copied and pasted the code into Excel from my earlier message into a new module in Personal.xlsb and it worked as intended (see below).
    2. Yes - you're right. Sorry about that.
    3. You can use Send without Display is you are simply writing a text variable to a range as in this example. Display here is merely used to avoid sending unwanted test messages.
    wdDoc and oRng are both declared as objects in the code before you set them to their values, so I am not sure what is going on there. Sorry.

    message.jpg
    Graham Mayor - MS MVP (Word) 2002-2019
    Visit my web site for more programming tips and ready made processes
    http://www.gmayor.com

  15. #15
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    so I am not sure what is going on there. Sorry.
    Not a problem at all -- it's cold and snowing a little here so it'll give me something to do besides shoveling
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  16. #16
    VBAX Newbie
    Joined
    Feb 2015
    Posts
    1
    Location
    this is very nice post
    Try out our free Pass4sure - ccna security pdf and latest comptia stevenson.edu training courses to get high flying success in final and bfit.edu exams

Posting Permissions

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