Forum Discussion
vba sending email w/ attachment
- Mar 27, 2019You cannot add attachments by adding the path to the body, you need the .AddAttachment method:
.AddAttachment "C:\Users\tabor\Documents\" & WBname & ".xlsx"
.AddAttachment "C:\Users\tabor\Documents\" & WBname & ".xlsx"
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.
- JKPieterseMar 27, 2019Silver ContributorPerhaps you can find what you need here: http://rondebruin.nl/win/s1/cdo.htm
- katrina betheaMar 27, 2019Brass Contributor
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:=WBnameWorkbooks("blacklist system").Activate
Range("A1:F150").Select
Selection.CopyWorkbooks(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 StringstrSubject = "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_HandlingSet CDO_Config = CreateObject("CDO.Configuration")
CDO_Config.Load -1Set 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 WithWith CDO_Mail
Set .Configuration = CDO_Config
End WithCDO_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.SendError_Handling:
If Err.Description <> "" Then MsgBox Err.DescriptionEnd 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.
- kinuasaMar 28, 2019Brass Contributor
Hi,
Message object is not have a Attachment property.
there is an https://docs.microsoft.com/en-us/previous-versions/exchange-server/exchange-10/ms528117%28v%3dexchg.10%29 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