A real challenge

I am looking to do something a bit clever using Excel and OutlookXP and need help with the code, i hope one of you smart chaps can help me out.

I would like to send a standard email saved somewhere (suggestions?) - and an attachment - to a recipient. I will be taking email addresses down in cell "A1" of my spreadsheet and i want a macro that will send the email and attachment to the recipient whose address will be in "A1"

If anyone could provide the code to do this that would be great.

Regards,

Tompy

Open the file that has the list of names.

Turn on the macro recorder (tools > macro > record)

click on the first name in the list

hold down the Ctrl key and press the down arrow

open the file you want as an attachment

select File > Send to

turn off the macro recorder

go into VBE (tools > macro > visual basic editor) or press F11

change the command that says: Selection.End(xlDown).Select
To say something like: lastRow = Range("A1").End(xlDow).Row

use that to create a loop

for myRow = 2 to lastRow

Next myRow

inside the loop put the command for the File > Send to

you should not have to open the attachment file every time

- Pete

Thanks Pete

That makes sense but my code knowledge is not good enough to set up the loop and to talk to Outlook - I was hoping someone could provide the full code, i can adapt it to the right references once i have it, but i have never seen a working version which is what i will use as a template,

Regards,

Marcus

This may help.

Sub sendmymail()
''' This will send the workbook along with macros ''''
ActiveWorkbook.HasRoutingSlip = True
With ActiveWorkbook.RoutingSlip
.Delivery = xlOneAfterAnother
.Subject = "Your Subject Here"
.Message = "Your Mesage here"
.Recipients = Worksheets("Sheet1").Range("A1").Text
.ReturnWhenDone = False
ActiveWorkbook.Route
End With
End Sub
Sub sendwithoutmacro()
'' This will send workbook with out macros it sends a copy of the sheet you want to send ''

Sheets("Sheet1").Select '''create the work sheet to be sent
Sheets("Sheet1").Copy

ActiveWorkbook.HasRoutingSlip = True
With ActiveWorkbook.RoutingSlip
.Delivery = xlOneAfterAnother
.Subject = "Your Subject Here"
.Message = "Your Mesage here"
.Recipients = Worksheets("Sheet2").Range("A1").Text '' I changed to sheet2 for address
.ReturnWhenDone = False
ActiveWorkbook.Route
End With
ActiveWorkbook.Saved = True
ActiveWindow.Close
End Sub

HTH

Charles