Outlook Macro sends to some email contact recipients and not others

[micheldun]

I've written a very simple macro to send a workbook as an email from the workbook. There are 14 workbooks that I applied this macro to. Most of them send fine, but others do not send at all. I've narrowed it down to probably being something wrong in the .to field in Outlook 365. The formula pulls in the contact's name and places it in the .to box. I could not find anything different within the contacts to explain this. I'm using Excel 2013 and Outlook 365. The VBA follows:

Sub SendMail()

    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 = Sheets("Sheet2").Range("A2").Value
        .CC = ""
        .BCC = ""
        .Subject = Sheets("Sheet2").Range("B2").Value
        .Body = Sheets("Sheet2").Range("C2").Value
        .Attachments.Add ActiveWorkbook.FullName
        
        '.Attachments.Add ("C:\test.txt")
        .Send
    End With
    On Error GoTo 0
    
    ActiveWorkbook.Save
    ActiveWorkbook.Close

    Set OutMail = Nothing
    Set OutApp = Nothing
End Sub

Thanks,
Michel

 

[pbornemeier]

My guess would be that the addresses were not quite correct. Comment out the .Send line and make OutApp.visible then review the email in the now visible outlook and use the check names function in outlook to validate (or invalidate) the addresses.

 

[micheldun]

Thank you pbornemeier. The contacts that would not send were group contacts. It turned out a couple of the names were too similar to other names in my contact list. All I had to do was change the group name to something unique.

Michel

- Display worksheets using Excel Jeanie or HTML Maker
- Test and validate results for all code on a copy of your worksheet!! How do you use the code you just found?
- Posting guidelines, forum rules and terms of use
- Try searching for your answer first, see how
- Read the FAQs
- List of BB codes (use CODE to keep your code formatted)