VBA string to attach file to email with dynamic filename for attachment

[H Haimes]

Hi there,
first ever post on such a forum, so hope you guys can assist.

I am trying to create and then attach a dynamic file to a series of emails. Code below has been cobbled together from other threads and creates the email but without any attachment. Help!

Sub Test1()


Dim i As Long, lastRow As Long


Set from_sheet = Sheets("Data")
Set to_sheet = Sheets("Data")


lastRow = from_sheet.Cells(Rows.Count, "A").End(xlUp).Row


For i = 5 To lastRow
'test if cell is empty
If from_sheet.Range("A" & i).Value <> "" Then
to_sheet.Range("A2").Value = from_sheet.Range("A" & i).Value

Sheets(Array("Letter", "P11D page1", "P11D page2")).Select

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

Dim FName As String
Dim Email As String

FName = Sheets("Letter").Range("g50").Text
Email = Sheets("Data").Range("f2").Text

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, FileName:=FName _
, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
:=False, OpenAfterPublish:=False

With OutMail
.To = Email
.CC = ""
.BCC = ""
.Subject = "P11D draft attached"
.Body = "please find attached draft P11D and accompanying letter. This requires you to review and raise any queries within 7 days. If you have any questions please don't hesitate to contact me direct, kind regards, "
.Attachments.Add FName
If Send = True Then
.Send
Else
.display
End If
End With
On Error GoTo 0


Set OutMail = Nothing
Set OutApp = Nothing


End If
Next i

End Sub

 

[RickXL]

Hi and welcome to the MrExcel Message Board.

I always use Option Explicit to make sure that all the variables and objects are defined as I expect so I re-wrote your code slightly:

Option Explicit

Sub Test1()
    Const Send      As Boolean = False
    Dim i           As Long
    Dim lastRow     As Long
    Dim FName       As String
    Dim Email       As String
    Dim from_sheet  As Worksheet
    Dim to_sheet    As Worksheet
    Dim outApp      As Object
    Dim OutMail     As Object
    
    Set from_sheet = Sheets("Data")
    Set to_sheet = Sheets("Data")
    
    lastRow = from_sheet.Cells(Rows.Count, "A").End(xlUp).Row
    
    For i = 5 To lastRow
        'test if cell is empty
        If from_sheet.Range("A" & i).Value <> "" Then
            to_sheet.Range("A2").Value = from_sheet.Range("A" & i).Value
            
            Sheets(Array("Letter", "P11D page1", "P11D page2")).Select
            
            Set outApp = CreateObject("Outlook.Application")
            Set OutMail = outApp.CreateItem(0)
            
            FName = Sheets("Letter").Range("g50").Text
            Email = Sheets("Data").Range("f2").Text
            
            ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=FName _
                , Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
                :=False, OpenAfterPublish:=False
            
            With OutMail
                .To = Email
                .CC = ""
                .BCC = ""
                .Subject = "P11D draft attached"
                .Body = "please find attached draft P11D and accompanying letter. This requires you to review and raise any queries within 7 days. If you have any questions please don't hesitate to contact me direct, kind regards, "
                .Attachments.Add FName
                If Send = True Then
                    .Send
                Else
                    .display
                End If
            End With
            
            Set OutMail = Nothing
            Set outApp = Nothing
        
        End If
    Next i
End Sub

(Please use the [ code ] and [ /code ] tags when posting code. It makes it easier to read.)

Apart from the fact that the Send variable was not set anywhere it looks pretty much OK to me. (I set it as a constant for test purposes.)

So, I suspect that the issue is with the file name in cell G50 of the Letter worksheet. If I placed a valid file name in there with the full path (e.g. C:\Users\RickXL\Documents\@Excel\Letter.pdf) then I get an email with an attachment, called Letter.pdf.)

There are lots of ways to specify the file name (e.g. full name in cell, hard-coded in VBA, part VBA - part worksheet, looked up using various functions etc). I think you just need to pick one and use it everywhere.

If it is not that then please tell me what you are putting into G50 and I will take another look.


Regards,

 

[H Haimes]

Many thanks RickXL. Works a treat.

 

[RickXL]

Great news!

Thanks for letting me know.


Regards,