Does anyone have Excel VBA code to email a file as an attachment in Outlook
2000 using a variable? The file name is not static. It changes every month.
Please note the code below, which is not executed, where it reads:
'.Attachments.Add ("C:\test.txt"). I want to use a variable instead of the
hard-coded path and file "C:\test.txt".


Sub Mail_workbook_Outlook()
'This example sends the last saved version of each open workbook
'You must add a reference to the Microsoft outlook Library
Dim OutApp As Outlook.Application
Dim OutMail As Outlook.MailItem
Dim Wb As Workbook
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(olMailItem)
With OutMail
.To = "
.CC = ""
.BCC = ""
.Subject = "Monthly File Attachment"
.Body = "Hi there"
For Each Wb In Application.Workbooks
If Wb.Windows(1).Visible And Wb.Path < "" Then
.Attachments.Add Wb.FullName
End If
Next
'You can add other files also like this
'.Attachments.Add ("C:\test.txt")
.Display 'or use .Display
End With
Set OutMail = Nothing
Set OutApp = Nothing
End Sub