Excel VBA to create thunderbird email with attachment 1

[rogerte]

If have an Excel 2007 workbook that on one page holds holds invoice data (name, invoice items and eMail address) and the other page contains an Invoice template.

I have created a VBA macro that runs through every line of the invoice data worksheet, filling the template with the required data and then saving the invoice template sheet as a PDF using the name field as the PDF name (e.g. "Joe Bloggs.PDF"). This creates up to 300 individual PDF invoices.

Now I want to automatically email all the PDFs to the correct email address, if there is one, otherwise I want to print the PDF.

To complicate things up to 4 PDFs can go to an individual email address, although I am happy to send each one individually.

And to further complicate matters, we use Thunderbird as our email client.

Google hasn't been too helpful.

Can anyone point me in the right direction?

 

[Andrzejek]

If you already have all information needed to send an e-mail, like: From, To, Subject, Body, and Attachment(s) – have you considered using CDO? You can find some examples here at TT

Have fun.

---- Andy

 

[rogerte]

Andrzejek

Thanks for your reply, but what do you mean by CDO?

Cheers

Roger

 

[PHV]

Have a look here:
thread705-1369407

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Andrzejek]

This is what I use to send e-mails from my code.
You just need to set your cdoSMTPServer and pass some parameters into this Sub.

Public Sub SendAMessage(strFrom As String, strTo As String, _
    strCC As String, strSubject As String, strTextBody As String, _
    Optional strBcc As String, Optional strAttachDoc As String)

Dim objMessage As CDO.Message
[green]
'''Add Referebce to: Microsoft CDO For Windows 2000 Library
[/green]
On Error GoTo MyErrorHadler

Set objMessage = New CDO.Message

With objMessage
    .From = strFrom
    .To = strTo
    If Len(Trim$(strCC)) > 0 Then
        .CC = strCC
    End If
    If Len(strBcc) > 0 Then
        .BCC = strBcc
    End If[green]
    ''' On behalf of
    '.Sender = "Bill.Gates@msn.com"[/green]
    .Subject = strSubject
    .TextBody = strTextBody
    
    If Len(strAttachDoc) > 0 Then
        .AddAttachment strAttachDoc
    End If
    
    With .Configuration.Fields
        .Item(CDO.cdoSMTPServer) = [red]"YOUR.SMTP.SERVER.LAN"[/red]
        .Item(CDO.cdoSMTPServerPort) = 25
        .Item(CDO.cdoSendUsingMethod) = CDO.cdoSendUsingPort
        .Item(cdoSMTPConnectionTimeout) = 10
        .Update
    End With
    .Send
End With

Set objMessage = Nothing

Exit Sub
MyErrorHadler:
[green]
'' Do something here in case of an error
[/green]
Resume Next

End Sub

Have fun.

---- Andy

 

[rogerte]

Thanks for that - just what I needed.

Cheers

Roger

 

[Andrzejek]

You are welcome.
Just a word of cautious: you may want to check with your ‘guy in charge’ of mail server to allow your application to send e-mails this way. Some mail servers will not allow you to send multiple e-mails this way due to the possibility of sending a lot of SPAM.


Have fun.

---- Andy