Open Outlook from Excel and send attachments

  • Thread starter Thread starter Bob
  • Start date Start date
B

Bob

Hi,
Can someone send me a solutions for this or point me to a website where i
can find information for this.

I have an Excel 2002 sheet with two columns.
Column A is plain text, with an explanation about a certain PDF document.
Column B contains an hyperlink to a PDF document

In column C, i want to put, for example an 'X', after the documents which I
want to send with Outlook 2002.
Is it possible to write an Excel macro, which scans the column C looking
for the cells with an 'X' in it.
Then start Outlook and insert the documents, that the hyperlinks from
column B point to, as attachments.
Of course only the documents with an 'X' in column C.
The recipient and subject line doesn't have to be filled in.

Kind regards,

Bob
 
Bob,

Here is some code. I know that you said that the recipient and the subject
don't have to be filled in, but my example shows all of these. Do as you
will with that.

Sub SendMail()
Dim oOutlook As Object
Dim oMailItem As Object
Dim oRecipient As Object
Dim oNameSpace As Object
Dim cLastRow As Long
Dim i As Long

Set oOutlook = CreateObject("Outlook.Application")
Set oNameSpace = oOutlook.GetNameSpace("MAPI")
oNameSpace.Logon , , True

Set oMailItem = oOutlook.CreateItem(0)
With oMailItem
Set oRecipient = .Recipients.Add("(e-mail address removed)")
oRecipient.Type = 1 '1 = To, use 2 for cc
'keep repeating these lines with
'your names, adding to the collection.
.Subject = "The extract has finished."
.Body = "This is an automatic email notification"
'add attachments
cLastRow = Cells(Rows.Count, "B").End(xlUp).Row
For i = 1 To cLastRow
If LCase(Cells(i, 3).Value) = "x" Then
.Attachments.Add Cells(i, 2).Hyperlinks(1).Address
End If
Next i
.Display
End With

End Sub

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
And if I am not asking too much can you please modify it so that it can be
used for MS Outlook Express?
TIA
 
Sorry, but you are. OE doesn't expose an object model.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Back
Top