Forum Discussion
katrina bethea
Mar 26, 2019Copper Contributor
vba sending email w/ attachment
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
- You cannot add attachments by adding the path to the body, you need the .AddAttachment method:
.AddAttachment "C:\Users\tabor\Documents\" & WBname & ".xlsx"
7 Replies
Sort By
- JKPieterseSilver ContributorYou cannot add attachments by adding the path to the body, you need the .AddAttachment method:
.AddAttachment "C:\Users\tabor\Documents\" & WBname & ".xlsx"- katrina betheaCopper Contributor
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.
- JKPieterseSilver ContributorPerhaps you can find what you need here: http://rondebruin.nl/win/s1/cdo.htm