Running outlook from Excel

[Al]

I have a couple of question about running outlook from Excel

1) if I have an email address (NOT a hyperlink) written in a cell, is it
possible to run a macro which will take this address, open a mail window
with this address as the recipients address, put in a specified subject, and
attach a specified file? I don't want to use hyperlinks as I am in a shared
workbook and it gets complicated.

2) Is it possible to take a column of cells that hold a name, company name,
phone number and email, and automatically add it to the outlook contacts
list ?



thanks for the help in advance!

Allan

 

[Bernie Deitrick]

Allan,

For 1) below: The macro below requires a reference to the Outlook object, set through Tools | References... in the VBEditor.

This assumes that the email address is in cell A1 of the activesheet.

2) I haven't tried so I can't help you.

HTH,
Bernie
Excel MVP


Sub EmailNow()
Dim ol As Object
Dim myItem As Outlook.MailItem
Dim myMsg1 As String
Dim myAtts As Outlook.Attachments

Set ol = CreateObject("outlook.application")

'Create the message body
myMsg1 = "Hi Allan," & Chr(10) & Chr(10)
myMsg1 = myMsg1 & "Here's the file that will make you rich." & Chr(10) & Chr(10)
myMsg1 = myMsg1 & "Bernie" & Chr(10)

Set myItem = ol.CreateItem(olMailItem)
myItem.to = Range("A1").Value
myItem.Subject = "Enlarge your wallet, not your....."
myItem.Body = myMsg1
Set myAtts = myItem.Attachments
'Can also send other files, just use "C:\Folder\filename.ext"
myAtts.Add ThisWorkbook.FullName
myItem.Send

Set ol = Nothing

End Sub