Solved

Marco to create an email from Excel

Posted on 2012-03-14
3
446 Views
Last Modified: 2012-03-14
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_Outlook_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").SpecialCells(xlCellTypeVisible)
    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.Application")
    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
0
Comment
Question by:DavidH7470
3 Comments
 
LVL 13

Accepted Solution

by:
Shanan212 earned 500 total points
ID: 37720286
try

.Attachments.Add FiletoSend
0
 
LVL 28

Expert Comment

by:omgang
ID: 37720298
Instead of InsertFile try
.Attachments.Add(FiletoSend)

OM Gang
0
 

Author Closing Comment

by:DavidH7470
ID: 37720600
Perfect.  Thank you.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

932 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now