email via outlook from excel

[pdeshazier]

can you, via a formula or macro, invoke outlook and send an email from excel?

[Bob Phillips]

AN example

[vba]


Dim oOutlook As Object
Dim oMailItem As Object
Dim oRecipient As Object
Dim oNameSpace As Object


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


Set oMailItem = oOutlook.CreateItem(0)
Set oRecipient = _
oMailItem.Recipients.Add("bob.phillips@somewhere.com")
oRecipient.Type = 1 '1 = To, use 2 for cc
'keep repeating these lines with
'your names, adding to the collection.
With oMailItem
.Subject = "The extract has finished."
.Body = "This is an automatic email notification"
' .Attachments.Add ("filename") 'you only need this if
'you are sending attachments?
.Display 'use .Send when all testing done
End With
[/vba]

[pdeshazier]

thanks, but how do i tell it which email address to use based on the value of a cell?

[pdeshazier]

for instance, i'll have a workbook which has account errors listed, along with department directors' names in a cell on each row. i will need to email a range of data on the spreadsheet to whomever the director for that dept is (i can include their email address on each row if necessary). can you tell me how to accomplish this? thanks.

[Bob Phillips]

Show us an example workbook.

[pdeshazier]

i've attached a spreadsheet. the department dir email is who the recipient of each row's data will be, so i need to be able to read column H and grab each row that value exists on, then when the value in col H changes, launch an email. so, i need the macro to select rows 2-7 and email to heather.mcmurray@sumner.org, then select row 8 and email to cindy.oneal@sumner.org, then email the remaining rows to mandy.thompson@sumner.org and so on.

[figment]

i do a similar things with reports at my office, and with the help of some code from this site, i got it to work. this code relys on there bing a table that has everybodys/deppartemt listed, along with there email address. for my poupoues i keep this table on a hidden sheet with in the same workbook, but you can put it anywhere you like. i have addapted the code to fit your formate.

Attachment 8922

i hope this helps

[pdeshazier]

YOU'RE GENIUS! love it! how can i invoke it from a button on the spreadsheet rather than opening vb editor and running it???? thanks soooooo much

[pdeshazier]

one more question: i need the "from" user to be from a different email address than that of the user signed on. is there a way to set this? thanks so much for your help

[Bob Phillips]

You will have to sign in to that profile, the oNameSpace.Logon is where you do that.

[pdeshazier]

one more question. when the code creates each email, how can i timestamp each row, say, in column H? thanks.

[pdeshazier]

test

[pdeshazier]

i created this workbook in 2007 and saved as 2003 (.xls); however, i can't get it to run on users' PCs which have 2003 - i get 'error loading DLL.' do you know how to resolve this?

[pdeshazier]

also, how does 'whoto' work. is this a table name?

[pdeshazier]

i posted this several years ago. how can i locate the spreadsheet i had attached in my post? thanks.

[Kenneth Hobs]

Only post 7 has an attachment.

[pdeshazier]

Thanks. I'll start a new post.