Macro Help: Sending Email from VBA

[Gbrown92]

Hello,

I've got 2 workbooks. I need to write a macro that will be in workbook 1, and will requrie to attach workbook 1 to an email and deliver it to all receipients which have email addresses listed under "To"(A1), "CC"(B1), "BCC"(C1) columns as appropriate in workbook 2. The recipient list may need to expand or reduce at any time.

Is this a simple process?

Any help would be much appreciated.

Thanks,

Gerry

[Dammy]

Yes we can and I don't think it's complex! I have given you the draft (untested) version.

Dim Subject,
Send_From,
Body As String

Dim Mail_Object,
Mail_Single As Variant

Dim Rowcount,
i as integer

Workbooks.open("Workbook2.xls")
Workbooks("Workbook2.xls").worksheet("Sheet1").activate

With ActiveSheet
Rowcount = .Cells(.Rows.Count, "A").End(xlUp).Row
End With

Dim Send_To(Rowcount) as string
Dim Cc(Rowcount) as string
Dim Bcc(Rowcount) as string

Subject = "Trying to send email using VBA"
Send_From = "[email protected]"
Body = "You have successfully sent an e-mail using VBA !!!!"

for i = 1 to Rowcount
Send_To(i) = activesheet.cells(i,1)
Cc(i) = activesheet.cells(i,2)
Bcc(i) = activesheet.cells(i,3)

On Error GoTo debugs
Set Mail_Object = CreateObject("Outlook.Application")
Set Mail_Single = Mail_Object.CreateItem(0)

With Mail_Single
.Subject = Subject
.To = Send_To
.cc = Cc
.BCC = Bcc
.Body = Body
.send
End With
next i

debugs:
If Err.Description <> "" Then MsgBox Err.Description

[Gbrown92]

Dammy,

Thanks for having a crack at this - much appreciated. Please excuse my lack of VBA skills here (ie I may be doing something stupid).

When I enter that code as a Macro in Excel, it immediately highlights the following lines of code in red:

Dim Subject,
Send_From,

Dim Mail_Object,

Dim Rowcount,

Attempting to run the macro gives the following error:

Compile Error: Syntax Error

By removing the commas at the end of the lines, the error will change to Compile Error: Sub or Function Not Defined on the line of code Body As String.

By changing this to DIM Body As String, I was able to get the macro to run to the line of code Mail_Single As Variant, where the error Compile Error: Statement invalid outside Type Block occurred.

Not entirely sure what I was doing by making these changes, just thought I'd play around with it to see what I can do myself.

Looking forward to any help that anybody can provide to take this forward.

Thanks,

Gerry

[dmasters4919]

Dammy,

Thanks for having a crack at this - much appreciated. Please excuse my lack of VBA skills here (ie I may be doing something stupid).

When I enter that code as a Macro in Excel, it immediately highlights the following lines of code in red:

Dim Subject,
Send_From,

Dim Mail_Object,

Dim Rowcount,

Attempting to run the macro gives the following error:

Compile Error: Syntax Error

By removing the commas at the end of the lines, the error will change to Compile Error: Sub or Function Not Defined on the line of code Body As String.

By changing this to DIM Body As String, I was able to get the macro to run to the line of code Mail_Single As Variant, where the error Compile Error: Statement invalid outside Type Block occurred.

Not entirely sure what I was doing by making these changes, just thought I'd play around with it to see what I can do myself.

Looking forward to any help that anybody can provide to take this forward.

Thanks,

Gerry

I changed a few things that will hopefully fix the problems

Please Login or Register  to view this content.

[Gbrown92]

dmasters4919,

That code seems to be an improvement. Only one line of code was red:

Please Login or Register  to view this content.

Surely this is because "To" is not defined anywhere? Does this mean I need to use a DIM statement to define it in some way?

Thanks for the help so far. I'm going to learn VBA on the back of this so I can help myself next time!

Gerry

[dmasters4919]

dmasters4919,

That code seems to be an improvement. Only one line of code was red:

Please Login or Register  to view this content.

Surely this is because "To" is not defined anywhere? Does this mean I need to use a DIM statement to define it in some way?

Thanks for the help so far. I'm going to learn VBA on the back of this so I can help myself next time!

Gerry

There shouldn't be any red lines if it's pasted correctly. I tested the entire thing and it worked... What exactly is the error given when you press F8 to start the sub?

[Gbrown92]

Oops! It was pasted incorrectly - my mistake. This seems to have worked nicely but I've only got one email in the list. I'll add the circulation properly and feed back. The only thing it now needs to do is attach workbook 1. Can this be implemented easily?

Thanks

[dmasters4919]

Oops! It was pasted incorrectly - my mistake. This seems to have worked nicely but I've only got one email in the list. I'll add the circulation properly and feed back. The only thing it now needs to do is attach workbook 1. Can this be implemented easily?

Thanks

Oh yes I forgot you wanted it to be attached too. That is simple. Insert an attach line after .body, followed by the FULL file path (same one you use to open a workbook. So it would look like this

Please Login or Register  to view this content.

[Dammy]

oh yes! sorry for the mess as i drafted the code in hurry!! but dmasters4919's corrected solution would work fine!!!

[Gbrown92]

Sorry to resurrect an old thread - but I moved away from this code for a while and have just picked it back up.

I have managed to adapt the code that you have given me to suit my needs (almost!) - thanks. The last remaining problem is that the macro sends individual emails to data in each row of the spreadsheet because of the way that it loops.

I'm looking for it to send one email to all of the contacts within the range at once. Can you help with this? A sample of the code I've got is:

Please Login or Register  to view this content.

[Dammy]

Happy that it worked and just modify the loop as shown below,

For i = 2 To Rowcount
Send_To = Send_To & ";" & ActiveSheet.Cells(i, 1)
Cc = Cc & ";" & ActiveSheet.Cells(i, 2)
Bcc = Bcc & ";" & ActiveSheet.Cells(i, 3)
Next i

On Error GoTo debugs
Set Mail_Object = CreateObject("Outlook.Application")
Set Mail_Single = Mail_Object.CreateItem(0)

With Mail_Single
.Subject = Subject
.To = Send_To
.Cc = Cc
.Bcc = Bcc
.Body = Body
.Attachments.Add "Workbook.xls"
.Send
End With

Hope it will serve ur purpose.

Thanks,
Dammy.