excel vba linking outlook

Forum home » Delegate support and help forum » Microsoft VBA Training and help » Excel VBA Linking to OUTLOOK Email

Excel VBA Linking to OUTLOOK Email

resolvedResolved · Urgent Priority · Version 2010

Shamin has attended:
Excel VBA Intro Intermediate course
Excel VBA Advanced course

Excel VBA Linking to OUTLOOK Email

Hi Anthony,

Can I have some information related to Ecal VBA linking to Outlook email?

Attended course Excel VBA Advanced in Bloomsbury in Limehouse.

Regards
Shamim Akhtra

RE: Excel VBA Linking to OUTLOOK Email

Hi Shamin, thanks for your query. This subroutine contains the nuts and bolts of what you'll need:

*******************

Sub EmailWithOutlook()
'Variable declaration
Dim oApp As Object, _
oMail As Object, _
WB As Workbook, _
FileName As String

'Turn off screen updating
Application.ScreenUpdating = False

'Make a copy of the active sheet and save it to
'a temporary file
ActiveSheet.Copy
Set WB = ActiveWorkbook
FileName = "Temp.xls"
On Error Resume Next
Kill "C:\" & FileName
On Error GoTo 0
WB.SaveAs FileName:="C:\" & FileName

'Create and show the outlook mail item
Set oApp = CreateObject("Outlook.Application")
Set oMail = oApp.CreateItem(0)
With oMail
'Uncomment the line below to hard code a recipient
'.To = "someone@somedomain.com"
'Uncomment the line below to hard code a subject
'.Subject = "Look at my workbook!"
.Attachments.Add WB.FullName
.Display
End With

'Delete the temporary file
WB.ChangeFileAccess Mode:=xlReadOnly
Kill WB.FullName
WB.Close SaveChanges:=False

'Restore screen updating and release Outlook
Application.ScreenUpdating = True
Set oMail = Nothing
Set oApp = Nothing
End Sub

*******************

Hope this helps,

Anthony

RE: Excel VBA Linking to OUTLOOK Email

Hi,

Thanks for the information, and prompt response.

Kind regards
Shamim

 

Training courses

 

Training information:

Welcome. Please choose your application (eg. Excel) and then post your question.

Our Microsoft Qualified trainers will then respond within 24 hours (working days).

Frequently Asked Questions
What does 'Resolved' mean?

Any suggestions, questions or comments? Please post in the Improve the forum thread.


 

VBA tip:

Count the Rows and Columns in a Selection

If you need to count the number of rows or columns in a worksheet use the following code:

Selection.Rows.Count - Returns the number of rows in the selection

Selection.Columns.Count - Returns the number of columns in the selection

Selection.CurrentRegion.Rows.Count - Returns the number of rows in the current region of the selection

View all VBA hints and tips


Server loaded in 0.1 secs.