vba sending email w/ attachment

[katrina bethea]

long bit of code and im sure you all don't need all of it but its here for reference. The email portion works fine when under strBody i have text or a range with "". the problem im having is with getting it to upload a file path for an attachment. do i have to change all my code or is there just a slight typo.

the WBname portion of it is a public variable defined in a different module and works like it should within its own module (the following module).

Option Explicit
Public Sub BlkLst_Save()

Public WBname As String
WBname = "BlackList" & ActiveSheet.Name & Format(Now(), "MMM dd, yyyy")

Workbooks.Add
ActiveWorkbook.SaveAs Filename:=WBname

Workbooks("blacklist system").Activate
Range("A1:F150").Select
Selection.Copy

Workbooks(WBname).Activate
Range("A1").Select
ActiveSheet.Paste

End Sub

so the above works fine. Below is the email portion of this and runs into an error when trying to pull from that save path as previously stated. I have bolded and italicized the portion that errors out. (**** indicate deleted information for security)

Public Sub M_Emailer()
'sends email to Marik for blacklisted devices

Dim CDO_Mail As Object
Dim CDO_Config As Object
Dim SMTP_Config As Variant
Dim strSubject As String
Dim strFrom As String
Dim strTo As String
Dim strCc As String
Dim strBcc As String
Dim strBody As String

strSubject = "Blacklist From CDR"
strFrom = "*******"
strTo = "********"
strCc = ""
strBcc = ""
strBody = C:\Users\tabor\Documents\&WBname&.xlsx

Set CDO_Mail = CreateObject("CDO.Message")
On Error GoTo Error_Handling

Set CDO_Config = CreateObject("CDO.Configuration")
CDO_Config.Load -1

Set SMTP_Config = CDO_Config.Fields

With SMTP_Config
.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "smtp.gmail.com"
.Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1
.Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = "*******"
.Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "*******"
.Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 465
.Item("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = 1
.Item("http://schemas.microsoft.com/cdo/configuration/smtpconnectiontimeout") = 60
.Update
End With

With CDO_Mail
Set .Configuration = CDO_Config
End With

CDO_Mail.Subject = strSubject
CDO_Mail.From = strFrom
CDO_Mail.To = strTo
CDO_Mail.TextBody = strBody
CDO_Mail.CC = strCc
CDO_Mail.BCC = strBcc
CDO_Mail.Send

Error_Handling:
If Err.Description <> "" Then MsgBox Err.Description

End Sub

[#2 · Accepted solution]

You cannot add attachments by adding the path to the body, you need the .AddAttachment method:
.AddAttachment "C:\Users\tabor\Documents\" & WBname & ".xlsx"

[#3]

@Jan Karel Pieterse 

I can't seem to figure out how this one works. i tried to just copy and paste what you put with no success. Looked up quite a few articles and watched a few videos on adding attachments as well and they all have me asking more questions than answer being provided (what portion of code i actually need for attachments vs which ones are for the email) since the code for the email they use is quite different from mine.  I can only do the basics and learn by doing/seeing. 

not going to lie but the email code i used was just a copy and paste from a recent article with small changes. 

[#4]

Perhaps you can find what you need here: http://rondebruin.nl/win/s1/cdo.htm

[#5]

so heres what i have now.

Public Sub M_Emailer()
'creates the save file to send
Dim WBname As String
WBname = "BlackList" & ActiveSheet.Name & Format(Now(), "MMM dd, yyyy")

Workbooks.Add
ActiveWorkbook.SaveAs Filename:=WBname

Workbooks("blacklist system").Activate
Range("A1:F150").Select
Selection.Copy

Workbooks(WBname).Activate
Range("A1").Select
ActiveSheet.Paste
'sends email to Marik for blacklisted devices
Dim CDO_Mail As Object
Dim CDO_Config As Object
Dim SMTP_Config As Variant
Dim strSubject As String
Dim strFrom As String
Dim strTo As String
Dim strCc As String
Dim strBcc As String
Dim strBody As String
Dim strAtch As String

strSubject = "Blacklist From CDR"
strFrom = "email"
strTo = "email"
strCc = ""
strBcc = ""
strBody = WBname
strAtch = "C:\Users\tabor\Documents\" & WBname & ".xlsx"

Set CDO_Mail = CreateObject("CDO.Message")
On Error GoTo Error_Handling

Set CDO_Config = CreateObject("CDO.Configuration")
CDO_Config.Load -1

Set SMTP_Config = CDO_Config.Fields

With SMTP_Config
.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "smtp.gmail.com"
.Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1
.Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = "email"
.Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "pass"
.Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 465
.Item("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = 1
.Item("http://schemas.microsoft.com/cdo/configuration/smtpconnectiontimeout") = 60
.Update
End With

With CDO_Mail
Set .Configuration = CDO_Config
End With

CDO_Mail.Subject = strSubject
CDO_Mail.From = strFrom
CDO_Mail.To = strTo
CDO_Mail.TextBody = strBody
CDO_Mail.Attachment = strAtch
CDO_Mail.CC = strCc
CDO_Mail.BCC = strBcc
CDO_Mail.Send

Error_Handling:
If Err.Description <> "" Then MsgBox Err.Description

End Sub

now i get "object doesn't support this property or method" when it gets to the CDO_Mail.Attachment = strAtch line ? most of the articles im looking at utilize outlook and not CDO. The link you posted was one article i've gone over and even downloaded his modules to go through them and try to decode them but what he has written down isn't notated very well to where someone as inexperienced as i can understand whats going on. all this is way over my head and why im asking for help.

[#6]

Hi,

Message object is not have a Attachment property.
there is an Attachments property, but it is read only.

Use the AddAttachment method when you want to attach a file.

https://devblogs.microsoft.com/scripting/how-can-i-attach-a-file-to-an-email-sent-using-cdo/

Regards,
kinuasa

[#7]

no sure if i typed something in wrong before or what but now its working???  thx for the help guys! took out all my other code that pertained to trying and uploading an attachment and just added this one piece at the bottom:

CDO_Mail.Subject = strSubject
CDO_Mail.From = strFrom
CDO_Mail.To = strTo
CDO_Mail.TextBody = strBody
CDO_Mail.AddAttachment "C:\Users\tabor\Documents\" & WBname & ".xlsx"
CDO_Mail.CC = strCc
CDO_Mail.BCC = strBcc
CDO_Mail.Send

thought i had already tried that and it wasn't working. Not going to mess with it anymore that's for sure lol.

[#8]

Excellent, thanks for letting us know.