DavidH7470
asked on
Marco to create an email from Excel
Hello:
I use the following macro to insert cells from a sheet in a workbook and create an email. I wish to add an attachment to the email. I added .InsertFile which seems correct but it does not pick up the file. I have tried using
.InsertFile = "C:\MyFile.pdf" as well creating a variable
Dim FiletoSend As String
FiletoSend = "C:\MyFile.pdf"
Which I realize is the same thing. I tried dropping the .pdf and still no luck.
Below is the entire code
FYI I did not write this code some really smart person did.
Sub Mail_Selection_Range_Outlo ok_Body()
' Don't forget to copy the function RangetoHTML in the module.
' Working in Office 2000-2007
Dim rng As Range
Dim rng1 As Range
Dim rng2 As Range
Dim FiletoSend As String
Dim EAddress As String
'EMAIL ADDRESS
EAddress = Sheets("Sheet1").Cells(1, 1).Value
'File Attachment
FiletoSend = "C:\MyFile.pdf"
Dim EMessage As String
EMessage = "Test Email"
Dim OutApp As Object
Dim OutMail As Object
Set rng = Nothing
On Error Resume Next
'Only the visible cells in the selection
Set rng = Sheets("Sheet1").Range("C4 :K26")
'Selection.SpecialCells (xlCellTypeVisible)
'You can also use a range if you want
'Set rng = Sheets("YourSheet").Range( "D4:D12"). SpecialCel ls(xlCellT ypeVisible )
On Error GoTo 0
If rng Is Nothing Then
MsgBox "The selection is not a range or the sheet is protected" & _
vbNewLine & "please correct and try again.", vbOKOnly
Exit Sub
End If
With Application
.EnableEvents = False
.ScreenUpdating = False
End With
Set OutApp = CreateObject("Outlook.Appl ication")
OutApp.Session.Logon
Set OutMail = OutApp.CreateItem(0)
On Error Resume Next
With OutMail
.To = EAddress
.CC = ""
.BCC = ""
.Subject = EMessage
.HTMLBody = RangetoHTML(rng)
.InsertFile = FiletoSend
.Send 'or use .Display
End With
On Error GoTo 0
With Application
.EnableEvents = True
.ScreenUpdating = True
End With
Set OutMail = Nothing
Set OutApp = Nothing
End Sub
I use the following macro to insert cells from a sheet in a workbook and create an email. I wish to add an attachment to the email. I added .InsertFile which seems correct but it does not pick up the file. I have tried using
.InsertFile = "C:\MyFile.pdf" as well creating a variable
Dim FiletoSend As String
FiletoSend = "C:\MyFile.pdf"
Which I realize is the same thing. I tried dropping the .pdf and still no luck.
Below is the entire code
FYI I did not write this code some really smart person did.
Sub Mail_Selection_Range_Outlo
' Don't forget to copy the function RangetoHTML in the module.
' Working in Office 2000-2007
Dim rng As Range
Dim rng1 As Range
Dim rng2 As Range
Dim FiletoSend As String
Dim EAddress As String
'EMAIL ADDRESS
EAddress = Sheets("Sheet1").Cells(1, 1).Value
'File Attachment
FiletoSend = "C:\MyFile.pdf"
Dim EMessage As String
EMessage = "Test Email"
Dim OutApp As Object
Dim OutMail As Object
Set rng = Nothing
On Error Resume Next
'Only the visible cells in the selection
Set rng = Sheets("Sheet1").Range("C4
'Selection.SpecialCells (xlCellTypeVisible)
'You can also use a range if you want
'Set rng = Sheets("YourSheet").Range(
On Error GoTo 0
If rng Is Nothing Then
MsgBox "The selection is not a range or the sheet is protected" & _
vbNewLine & "please correct and try again.", vbOKOnly
Exit Sub
End If
With Application
.EnableEvents = False
.ScreenUpdating = False
End With
Set OutApp = CreateObject("Outlook.Appl
OutApp.Session.Logon
Set OutMail = OutApp.CreateItem(0)
On Error Resume Next
With OutMail
.To = EAddress
.CC = ""
.BCC = ""
.Subject = EMessage
.HTMLBody = RangetoHTML(rng)
.InsertFile = FiletoSend
.Send 'or use .Display
End With
On Error GoTo 0
With Application
.EnableEvents = True
.ScreenUpdating = True
End With
Set OutMail = Nothing
Set OutApp = Nothing
End Sub
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Perfect. Thank you.
.Attachments.Add(FiletoSen
OM Gang