Email spreadsheets

[jaypatel]

Hi all,

I have the following data:

in column a, i have a list of email addresses;
in column b, i have a list of spreadsheets

eg

jpatel@test.co.uk; c:text.xls
mpatel@test.co.uk; c:name.xls


so i would need a script to send the text spreadsheet to the jpatel, and the name spreadsheet to mpatel.

Would also need to incorporate a message "YYYYY", and a subject line "XXXX"

can this be done?

Cheers

Jay

 

[Dannyh1]

Hi,

Are you using Outlook?

 

[jaypatel]

Yes, sorry, should have mentioned i am using outlook.

Jay

 

[Dannyh1]

Maybe somthing like this:

Dim Rng As Range
Set Rng = .Range("A1:A10")
    
Dim cl As Range
    
    For Each cl In Rng
 
    Set obOutApp = CreateObject("Outlook.Application")
    Set obOutMail = obOutApp.CreateItem(0)
   
    With obOutMail
       
        .To = cl.Value

        .CC = ""
        .BCC = ""
        .Subject = "Report" 
 
        .Body = "Hi Joe Bloggs"  & vbCrLf & " " & vbCrLf & _
        "Please find attached  report" & vbCrLf & _
        & "Kind Regards," & vbCrLf & "jaypatel"
        
        .Attachments.Add (cl.Offset(0, 1).Value)

        SendKeys "%{s}", True
        .Send
        'Sends
    End With
    
    Next cl

 

[jaypatel]

hi

say if i had a column c, where column c is the subject.

so, in c1, person x receiving admin payroll, the subject line would be November admin payroll; in c2, person y receiving marketing payroll, subject line would be November markting payroll.

the subject line in script above is the same, and need ability to make it different.

Cheers

Jay

 

[Dannyh1]

try:

.Subject = cl.Offset(0, 2).Value