Need help with auto email for Excel 2007

[marsbars]

Hi,

I am very new at this and any help would be greatly appreciated

I have whole list of tracking numbers on a spreadsheet. Is there a way to just auto email the "reference number", name and tracking number to the email in the D cell?

Step by step help is needed. I have attached an example file.test.xlsx

Thanks in advance for any help.

Marilyn

[marsbars]

Can anybody please help? any ideas?

[VBA FTW]

I hate when people do this, but read through these links:

http://www.rondebruin.nl/win/s1/outlook/amail4.htm
http://www.rondebruin.nl/win/s1/outlook/bmail2.htm

If you can't figure it out, I will get you something. But please read through this first, try it out, and let me know

[marsbars]

Hi,

Thanks for your reply.

I did read through that and tried it but it didn't work for my case and nothing happened. Been trying to figure it out for the last two hours now.

[VBA FTW]

Ha, welcome to VBA! I'll take a look this evening when I get home and see what I can do for ya

[marsbars]

Thank you very much! greatly appreciate it.

[VBA FTW]

This will get you started. Make sure you have Outlook open BEFORE pressing the button. Let me know if you want it tweaked more, as this will only pull what's in the 2nd row

[marsbars]

Thank you this is what I was looking for. Sorry, I am so new at this and trying to learn how this works. How do I go about pulling the 3 row on a completely separate email?

[marsbars]

Thanks for the help! Got it to work, this is what I ended up with.


Sub SendEmail(what_address As String, subject_line As String, mail_body As String)

Dim olApp As Outlook.Application
Set olApp = CreateObject("Outlook.Application")

Dim olMail As Outlook.MailItem
Set olMail = olApp.CreateItem(olMailItem)

olMail.To = what_address
olMail.Subject = subject_line
olMail.Body = mail_body
olMail.Send

End Sub
Sub SendMassEmail()

row_number = 1

Do
DoEvents
row_number = row_number + 1
Dim mail_body_message As String
Dim name As String
Dim Rma_number As String
Dim tracking_number As String

mail_body_message = Sheet1.Range("E2")
name = Sheet1.Range("B" & row_number)
Rma_number = Sheet1.Range("A" & row_number)
tracking_number = Sheet1.Range("D" & row_number)
mail_body_message = Replace(mail_body_message, "replace_name", name)
mail_body_message = Replace(mail_body_message, "Rma_number_replace", Rma_number)
mail_body_message = Replace(mail_body_message, "tracking_number_replace", tracking_number)
Call SendEmail(Sheet1.Range("C" & row_number), "XFX Tracking number", mail_body_message)

MsgBox "Complete!"

Loop Until row_number = 5

End Sub