Generate emails from Excel

[ismailr]

Hi,
In a sheet Col A, B, C and D contains Names, email address, subject and file path for attachment respectively.
Can some one help me to write VBA to generate the single email for each row from MS Outlook to the respective addressee along with the attachements.
thanks in anticipation.

[mvidas]

ismailr,

This should do the trick:Sub MailExample()
Dim olApp As Object, olOpen As Boolean, CLL As Range
olOpen = True
On Error Resume Next
Set olApp = GetObject(, "outlook.application")
On Error GoTo 0
If olApp Is Nothing Then
olOpen = False
Set olApp = CreateObject("outlook.application")
End If
For Each CLL In Range("B2", Cells(Rows.Count, 2).End(xlUp)).Cells
With olApp.CreateItem(0) '0=mailitem
.To = CLL.Text
.Subject = CLL.Offset(0, 1).Text
.Attachments.Add CLL.Offset(0, 2).Text
.Display
'.Send
End With
Next
If Not olOpen Then olApp.Quit
Set olApp = Nothing
End SubCheers
Matt

[matthewspatrick]

Matt,

I don't know if this is a best practice, but I doubt it's necessary to look for an open instance of Outlook first.

Apparently, only one instance of Outlook can ever be open, and if you use CreateObject when an instance is already running, it will simply return a reference to that already-open instance. So I always just use CreateObject.

Then, at the end, I do not bother to Quit the Outlook.Application. Rather, I just set the object variable to Nothing. If Outlook was already running, it continues to run, and in my experience, if Outlook was not running before, it does shut itself down.

Do you get the same result?

[mvidas]

Same result, yes. I assumed because I can have two explorer windows open on my taskbar that it had two instances, but looking at the task manager I see that is not the case. I suppose I just have that in there out of habit from using excel :dunno Thanks!
In that case...Sub MailExample()
Dim olApp As Object, CLL As Range
Set olApp = CreateObject("outlook.application")
For Each CLL In Range("B2", Cells(Rows.Count, 2).End(xlUp)).Cells
With olApp.CreateItem(0) '0=mailitem
.To = CLL.Text
.Subject = CLL.Offset(0, 1).Text
.Attachments.Add CLL.Offset(0, 2).Text
.Display
'.Send
End With
Next
Set olApp = Nothing
End Sub :)