Email a file as an attachment in Outlook 2000 using Excel VB macro

[Freddy]

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 = "(e-mail address removed)"
.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

 

[Ron de Bruin]

Hi Freddy

Use

..Attachments.Add Range("C1").Value

Or

..Attachments.Add MyVar

 

[Freddy]

My test file name 0706mira.zip. I do change directory to the folder to check
for its existence.

To get the file name to be emailed, I use:
RfileType1 = "????mira.zip"
workfile = Dir(RfileType1)
..Attachments.Add workfile

The system responds saying it cannot find the file, which is in the expected
folder.

 

[Ron de Bruin]

This small test is working for me.
Good night, bed time for me


Sub Test()
' Is working in Office 2000-2007
Dim OutApp As Object
Dim OutMail As Object
Dim strbody As String
Dim myvar As String

myvar = Application.DefaultFilePath & "\" & "test.txt"

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

strbody = "Hi there" & vbNewLine & vbNewLine & _
"This is line 1" & vbNewLine & _
"This is line 2" & vbNewLine & _
"This is line 3" & vbNewLine & _
"This is line 4"

On Error Resume Next
With OutMail
.To = "(e-mail address removed)"
.CC = ""
.BCC = ""
.Subject = "This is the Subject line"
.Body = strbody
If Dir(myvar) <> "" Then
.Attachments.Add myvar
End If
.display 'or use .Send
End With
On Error GoTo 0

Set OutMail = Nothing
Set OutApp = Nothing
End Sub

 

[Freddy]

The code you sent me works, however, it does not test for the existence of
the intended file to be attached. Therefore, I still have to customize it to
detect for the existence of the file before sending an email. You provided me
with the structure. I appreciate that. Thanks.

 

[Ron de Bruin]

It test it

 

[Freddy]

It apparently tests to see if the variable is assigned, not if the file
exists. Also, the code continues and either sends or displays the message
without the attachment.

 

[Freddy]

It test it

 

[Freddy]

This is a segment of the sample code I use to check for the existence of the
file that I plan to attach to the email:
Sub Test()
' Is working in Office 2000-2007
Dim OutApp As Object
Dim OutMail As Object
Dim strbody As String
Dim myvar As String
sourcedir = "C:\Tmp\"
ChDir sourcedir
RfileType1 = "????file.zip"
zipfile = Dir(RfileType1)
myvar = sourcedir & zipfile
Set OutApp = CreateObject("Outlook.Application")
OutApp.Session.Logon
Set OutMail = OutApp.CreateItem(0)
strbody = "This is the monthly zip file." & vbNewLine & _
"Line 1." & vbNewLine & _
"Thank you." & vbNewLine & vbNewLine _
On Error Resume Next
With OutMail
.To = "(e-mail address removed)"
.CC = ""
.BCC = ""
.Subject = "This is the subject line."
.Body = strbody

If Dir(myvar) <> "" Then
.Attachments.Add myvar
End If
.Display 'Displays the email dialog box but does not send the
email.
'.Send 'Sends the email without displaying the dialog box.
End With
On Error GoTo 0
Set OutMail = Nothing
Set OutApp = Nothing
end sub

 

[Ron de Bruin]

It will test if the file exist to avoid a error when you want to add it

It will send the mail with only text if the file not exist
Do you not want to send the mail when the file not exist ?

Then do the test after this line
myvar = Application.DefaultFilePath & "\" & "test.txt"

Try

Sub Test()
' Is working in Office 2000-2007
Dim OutApp As Object
Dim OutMail As Object
Dim strbody As String
Dim myvar As String

myvar = Application.DefaultFilePath & "\" & "test.txt"

If Dir(myvar) <> "" Then

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

strbody = "Hi there" & vbNewLine & vbNewLine & _
"This is line 1" & vbNewLine & _
"This is line 2" & vbNewLine & _
"This is line 3" & vbNewLine & _
"This is line 4"

On Error Resume Next
With OutMail
.To = "(e-mail address removed)"
.CC = ""
.BCC = ""
.Subject = "This is the Subject line"
.Body = strbody
.Attachments.Add myvar
.display 'or use .Send
End With
On Error GoTo 0

Set OutMail = Nothing
Set OutApp = Nothing
Else
MsgBox "Sorry file not exist"
End If
End Sub

 

[Freddy]

The code is ready and works. Thanks for your assistance.

 

[Ron de Bruin]

You are welcome