Problem with the e-mail code RRS feed

  • Question

  • i have a problem  with the e-mail creation with vba on excel. as shown below  the code during the debug doesn't stop for any reason but nothing happens.

     Dim OutApp As Object
        Dim OutMail As Object
        Set OutApp = CreateObject("Outlook.Application")
        Set OutMail = OutApp.CreateItem(0)

        On Error Resume Next

        With OutMail
            .To = "pippo, pluto"
            .CC = ""
            .BCC = ""
            .Subject = "ciao"
            .BodyFormat = olFormatHTML
            .HTMLbody = "<html><head></head><body> bau bau</body></html>"
        End With

        On Error GoTo 0
        Set OutMail = Nothing
        Set OutApp = Nothing

    this is only a part of a 600 rows code but i tried to use only this part in a new empty file and than it works.

    thanks to everybody who can help me.

    Friday, April 15, 2016 9:56 AM


All replies

  • Firstly in the VBA editor window have you selected Menu item Tools -> References and scrolled down until you find "Microsoft Outlook nn.0 Object Library" and checked the box against it and then OK. (nn is the version of Office you are using. eg Office 2010 would be 14.0)

    Next; when you have problems with the code you need to comment out the On Error Resume Next because when an error in the code is encountered, it simply ignores it and continues. You should not even use that code unless it is there for a specific purpose when an error can be expected and is being used for a specific purpose like testing if a worksheet exists by attempting to assign it to a worksheet object variable and if the sheet does not exist then it returns an error and you test for it.

    The error in the code is  .Attachments.Add     It is missing the path and file name. Should be something like the following.

        .Attachments.Add "C:\Users\User\Documents\Excel\Test Macros\Financial Year Formula.xlsm"

    In your example code it is encountering the .Attachments.Add error and just continuing with the next command.

    You indicated that the code is working on its own but it would not have been inserting an attachment but otherwise it was probably working so your other error is further down in the code so comment out any On Error Resume Next comments.

    You can also place MsgBox lines at strategic points (say 3 or 4 positions) in the code to see how far it goes. I usually use MsgBox 1, Msgbox 2 etc and the last one that displayed is how far the code got without stopping. You can then place some more MsgBox lines after where it stopped and continue this until you isolate the problem.

    Regards, OssieMac

    Friday, April 15, 2016 10:44 AM
  • for the attachments they have no problems, i copied wrong the code. for the ON ERROR GO TO 0 i tried but it still doesn't show anything, i also tried to activate the microsoft outlook library but it still doesn't work. sometimes outlook asks to choose the profile but when i choose the profile "outlook" and click "OK" outlook opens and closes immediately. for the msgboxes method i can't use it because the program doesn't stop but it does nothing. thanks for the answer
    Friday, April 15, 2016 12:03 PM
  • on error goto 0 turns the errorhandler off. Is this what you want?
    Friday, April 15, 2016 12:30 PM
  • no, i can't understand where is the problem because no error message is shown. the email part of the code copied and pasted in another empty file works, but within the other 600 code rows nothing happens. 
    Friday, April 15, 2016 12:36 PM
  • one more thing, in my pc with office 2010 every single thing works, but when i put the file in another pc with office 2016only the email doesn't work.
    Friday, April 15, 2016 1:03 PM
  • First get your errorhandlers right: or you include them or you write non and just let the application crash on a line (which is not so userfriendly, but OK for you to debug)

    Do'nt put errohandlers like "on error resume next" or "on error goto 0", because they will ignore the error.

    Friday, April 15, 2016 1:59 PM
  • thank you for the answer i've solved the problem. it was another thing not in the code rows.Thank you all!

    Friday, April 15, 2016 2:23 PM
  • Hi Fishermann,

    I am glad your issue has been resolved, it would be appreciated if you could share us your solution, mark it as answer, and then others who run into the same issue would find the solution easily.

    Best Regards,


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, April 18, 2016 3:21 AM