Outlook Does not recognize one or more names

[Tommy Snoberg Soderberg]

Hi,

I have this macro to mail individual mail to my recipients with attachment. I have not altered this macro and it have worked up til now. I cannot find any error in my mail-addresses either. Any suggestions what to look for?

Sub Email_From_Excel_Attachments()

    Dim emailApplication    As Object, emailItem As Object
    Dim cell                As Range, rng        As Range
    
    On Error GoTo myerror
    Set emailApplication = CreateObject("Outlook.Application")
    
    With Sheets("Adresses")
        lastrow = .Cells(.Rows.Count, "B").End(xlUp).Row - 1
        Set rng = .Cells(2, 2).Resize(lastrow, 1)
    End With
    
    For Each cell In rng.Cells
        Set emailItem = emailApplication.CreateItem(0)
    
        With emailItem
            .To = cell.Value
            .cc = cell.Offset(, 1).Value
            .Subject = cell.Offset(, 2).Value & Date
            .Body = cell.Offset(, 3).Value
            .Attachments.Add cell.Offset(, 4).Value
            .Send
        End With
        Set emailItem = Nothing
    Next cell
myerror:
    If Err <> 0 Then MsgBox (Error(Err)), 48, "Error"
End Sub

 

[RoryA]

Disable your error handler temporarily so that you can debug when the error occurs. Then you'll know which name(s) to check.

 

[Tommy Snoberg Soderberg]

What kind of error am I looking for? It has always worked before.

 

[RoryA]

An error in the email addresses/recipient names, judging by the error message.

 

[Tommy Snoberg Soderberg]

What I don't get is why it's become broken since I haven't changed any email-addresses.

 

[RoryA]

No way I can tell you that.

 

[Tommy Snoberg Soderberg]

Ok, I narrowed it down to two email addresses. They look correct and I have even pasted them in again in my excel-sheet just to be sure they are correct. They Work when I paste them directly into outlook but not when copied from my excel sheet / macro. Is there any way that these cells are copy protected or something? Could there be some cells are locked in my sheet?

 

[RoryA]

You're not copying anything so I don't see how that could be an issue. Try reinstating your error handler, but after the Msgbox line, put:

emailItem.Display

and check what is in the To field.

 

[Tommy Snoberg Soderberg]

You're not copying anything so I don't see how that could be an issue. Try reinstating your error handler, but after the Msgbox line, put:

emailItem.Display

and check what is in the To field.

Aha! Now I see the problem (I think). Outlook is changing the email-adress to the recipiants name since I have in in my adress book. When I did this extra step it had time to actually do this befor trying to send. You understand what I mean? Big thanks!

Just one more question now How do I make sure that example@ex.xx works and don't need to be changed to Name. Name in outlok?

 

[RoryA]

A literal email address should work regardless of whether it exists in your contacts. Can you actually send the email immediately after it displays?