Add attachment to the email

[Ankor]

Hi,
Here is my question. I am in Excel workbook that contains a list of users with their e-mail addresses. In VBA code I am looping through the list and send a slightly different e-mail to each user. I also want to be able to attach a Word file to my e-mail messages, but SendMail method does not seem to do it. Is there any other way?
Thank you.

 

[SkipVought]

Hi,

Check this function out

Public Function CdoSend( _
    MailTo As String, _
    MailFrom As String, _
    Subject As String, _
    MessageText As String, _
    Optional CC As String, _
    Optional BCC As String, _
    Optional FileAttachment As String) As Boolean
On Error GoTo CdoSend_Err

' This example use late binding, you don't have to set a reference
' You must be online when you run the sub
    Dim iMsg As Object
    Dim iConf As Object
    Dim Flds As Variant
 
    Set iMsg = CreateObject("CDO.Message")
    Set iConf = CreateObject("CDO.Configuration")
 
        iConf.Load -1    ' CDO Source Defaults
        Set Flds = iConf.Fields
        With Flds
            .Item("[URL unfurl="true"]http://schemas.microsoft.com/cdo/configuration/sendusing")[/URL] = 2
            .Item("[URL unfurl="true"]http://schemas.microsoft.com/cdo/configuration/smtpserver")[/URL] = "dfwmail.bh.textron.com"
            .Item("[URL unfurl="true"]http://schemas.microsoft.com/cdo/configuration/smtpserverport")[/URL] = 25
            .Update
        End With
 
    With iMsg
        Set .Configuration = iConf
        
        .To = MailTo
        .CC = CC
        .BCC = BCC
        .From = MailFrom
        
        .Subject = Subject
        .TextBody = MessageText

        
        If Len(FileAttachment & "") > 0 Then
            
            '## Last make sure the file actually exists and send it!:
            Dim fso
            Set fso = CreateObject("Scripting.FileSystemObject")
            If fso.FileExists(FileAttachment) Then
                .AddAttachment FileAttachment
            Else
                'otherwise return that the send failed and exit function:
                Debug.Print "[CdoSend.Error]=> File attachment path does not exist, quitting..."
                CdoSend = False
                Exit Function
            End If
        
        End If
    
        '## Send zee message! ##
        .Send
    
    End With

    Set fso = Nothing
    Set iMsg = Nothing
    Set iConf = Nothing
    
    CdoSend = True

CdoSend_Exit:
    Exit Function
    
CdoSend_Err:
    Debug.Print "[CdoSend.Error(" & Err.Number & ")]=> " & Err.Description
    CdoSend = False
    Resume CdoSend_Exit
End Function

Skip,

_[red][/red]

 

[Ankor]

Thanks. I though about something like that, but attempted to use CDONTS. When I use CDO, I get an error "The transport failed to connect to the server". It looks like something is wrong with SMTP settings, but I don't have access to alter them. CDONTS does not work either .

 

[SkipVought]

"Thanks. I though about something like that, but attempted to use CDONTS."

???

If I tried DONUTS and it didn't work, but there was something else that I just "thought about,"...

???

I DUNNO???

Skip,

_[red][/red]

 

[FaneDuru]

I tried the code and receve the same message:
"[CdoSend.Error(-2147220973)]=> The transport failed to connect to the server."...

 

[VulcanJedi]

does this method work for multiple attachments?

Tranpkp

 

[SkipVought]

http://msdn.microsoft.com/library/d...html/37be0471-06bd-489d-8bf2-5c22bb7ce17c.asp

Did you use your own settings in...

        With Flds
            .Item("[URL unfurl="true"]http://schemas.microsoft.com/cdo/configuration/sendusing")[/URL] = nn
            .Item("[URL unfurl="true"]http://schemas.microsoft.com/cdo/configuration/smtpserver")[/URL] = "mailserver.whatever.com"
            .Item("[URL unfurl="true"]http://schemas.microsoft.com/cdo/configuration/smtpserverport")[/URL] = mm
            .Update
        End With

Skip,

_[red][/red]

 

[Ankor]

Dear SkipVought,

There is something called "CDONTS.NewMail" that perfectly works from ASP page and does the same job as CDO. Tell me a little more about DONUTS, maybe I'll test it for you .

Yes, I used my own settings except of port that I did not know. After I fixed this issue the code started to work.

Dear tranpkp,

Yes, this method works for multiple attachments.

 

[SkipVought]

Then you answered your own question.

Skip,

_[red][/red]

 

[Ankor]

It sometimes happens to me . Thank you for the great code.