Forum Discussion

katrina bethea's avatar
katrina bethea
Copper Contributor
Mar 26, 2019
Solved

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

  • JKPieterse's avatar
    JKPieterse
    Silver Contributor
    You cannot add attachments by adding the path to the body, you need the .AddAttachment method:
    .AddAttachment "C:\Users\tabor\Documents\" & WBname & ".xlsx"
    • katrina bethea's avatar
      katrina bethea
      Copper Contributor

      JKPieterse 

      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. 

Resources