Send Email Issues...

[Willie03]

I have this macro here that is supposed to send emails to the people on column "C" and CC the people in column "F" and its supposed to attach a file located in my documents.

The issue that I'm having is that it does not display the emails of each of the people on column "C" nor does it attach the file.

NOTE: The attachment is the same for all emails that have to be sent.

Sub Send_Email()


    Dim OutApp As Object
    Dim OutMail As Object
    Dim strbody As String
    Dim cel As Range


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


    For Each cel In Range(("C2"), Range("C2").End(xlDown))
        strbody = "Hi there" & vbNewLine & vbNewLine & _
                "My name Is William" & vbNewLine & _
                "I work at Fair" & vbNewLine & _
                "Bye" & vbNewLine & _
                "WH"


        On Error Resume Next
        With OutMail
            .To = cel.Value    '"email.address@fair.com"
            .CC = cel.Offset(0, 3).Value
            .BCC = ""
            .Subject = "Information You Requested"
            .Body = strbody
            .Display
            '.Attachments.Add ("C:\test.txt")
            '.Send
        End With
        On Error GoTo 0
    Next cel


    Set OutMail = Nothing
    Set OutApp = Nothing
End Sub

 

[wigi]

Hello,

Do you understand that lines of code with a single quote in front ( ' ) are not executed? They are treated as commentary lines.

 

[Willie03]

Yes but even when unchecked it still does not attach the file...

 

[wigi]

- Does the file exist, exactly as mentioned?
- What happens if you put the line On Error Resume Next in commentary?

 

[wigi]

By the way, I would suggest this more condensed coding:

Sub Send_Email()

    Dim cel As Range

    On Error Resume Next

    For Each cel In Range(("C2"), Range("C2").End(xlDown))
        With CreateObject("Outlook.Application").CreateItem(0)
            .To = cel.Value    '"email.address@fair.com"
            .CC = cel.Offset(0, 3).Value
            .Subject = "Information You Requested"
            .Body = Replace("Hi there##My name Is William#I work at Fair#Bye#WH", "#", vbNewLine)
            .Display
            .Attachments.Add "C:\test.txt"
            '.Send
        End With
    Next

End Sub

 

[Willie03]

OK now its attaches the file 12 times to the last person on the list.

My list currently has 12 names and it should display each email with the attached file before it is sent.

 

[wigi]

I cannot reproduce what you are saying. I get 12 different emails, each one with 1 attachment.
Please test again, because for the attachment, first it did not work and later on it did work, while the code did not change.
I suspect that your testing procedure is not adequate.

 

[Willie03]

I honestly don't understand what I am doing wrong. Is it possible for you to show me the code you are using to compare?

 

[wigi]

It's in post #5 above...

 

[wigi]

Hello,

Any feedback on this please?
Thanks.