CODEWITHSUNDEEP
stringexcelvbaemail-attachments
hann783
hann783

Reputation: 51

Attaching variable file to VBA generated email

Thank you for all your help guys :) Code is now functioning.

I am still very new to VBA and am having some issues I would welcome advice on.

I have written some code so that dependent upon the value in a cell a varying email will be sent out to a particular person named in the sheet (each person will get one email). However, I would also like to attach a file specific to that person.

The file location will change weekly and the file name too as there is a date in the name.

The file location will be something like:

U:\My path\2016-08-08

and the file name

"Firstname Surname DD-MM-YYYY.xlsx"

(where Firstname Surname is earlier defined as "cell")

I came up with the following code (only relevant parts included):

   Dim DBlocation as variable
   Dim DBdate as variable
   Dim strfilename as string

   DBlocation = InputBox("Please copy file path to this week's dashboard files")
  DBdate = InputBox("Please type date from DB filename")

   strfilename = DBlocation & "\" & cell & " " & DBdate & ".xlsx" 

   .display
   .To = cell
   .SentOnBehalfOfName = "[email protected]"
   .Subject = cell & " Dashboard " & Format(Date, "DD-MMM-YY")
   .HTMLBody = strbody & vbNewLine & .HTMLBody

   .Attachments.Add (strfilename)
   .display

I have a textbox pop up with the filename and it looks right, but nothing is being attached to the email.

Could you advise how I should change the code to get the file attached?

Thank you so much in advance.

UPDATE

Code is now working as above.

I worked through your advice and made a couple of changes:

.Attachments.Add - I added an S after Attachment

I also amended an earlier part of code that I didn't list above

   For Each cell In sh.Columns("A").Cells.SpecialCells(xlCellTypeVisible)

this was previously xlCellTypeConstants

I have no idea why this worked but it has :) One super happy person here.

Upvotes: 0

Views: 3601

Answers (3)

Japhet Aureo
Japhet Aureo

Reputation: 11

try adding "s" to attachment. Should look like .attachments.add strFileName

Upvotes: 1

Brian
Brian

Reputation: 2108

Here is my attempt not knowing if your variables are valid:

Sub AttachFile()

Dim OutApp As Object
Dim OutMail As Object
Dim DBlocation As Variant 'Not "variable"
Dim DBdate As Variant 'Not "variable"
Dim strFileName As String

Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)

'ActiveWorkbook.Path  <== Maybe use this instead if it is the same as "this week's dashboard files"?
DBlocation = InputBox("Please copy file path to this week's dashboard files")
DBdate = InputBox("Please type date from DB filename")

strFileName = DBlocation & "\" & cell & " " & DBdate & ".xlsx"

With OutMail
    .To = cell
    .SentOnBehalfOfName = "[email protected]"
    .Subject = cell & " Dashboard " & Format(Date, "DD-MMM-YY")
    .HTMLBody = strBody & vbNewLine & .HTMLBody
    .Attachment.Add strFileName
    .display
End With

End Sub

I would also strongly suggest that you use Option Explicit at the top of your module to make certain you have declared all your variables (i.e. cell and strBody).

Upvotes: 0

user3598756
user3598756

Reputation: 29421

provided strfilename is a valid file name

.Attachment.Add strfilename

Upvotes: 0

Related Questions