emailing to multiple recipients

wratpack

Board Regular
Joined
Jan 4, 2005
Messages
83
Hi All, I've spent hours trawling through the board and trying different solutions with no success! I think I'm close but still no cigar. When I run the code below it works fine but says it doesn't recognice the mail recipient. The emails work fine as I can sent it to any one recipient using the basic .sendmail command but as soon as I try adding another recipient it stops. here is my code:

Sheet1.Copy
'names the new workbook
myPath = "C:\Documents and Settings\pwratislav\Desktop\"
myName = ActiveSheet.Name & " " & [d3] & " " & Format(Date, "dd-mmm-yy") & ".xls"
myName = myPath & myName
ActiveSheet.UsedRange.Value = ActiveSheet.UsedRange.Value
'and deletes the buttons
For Each shap In ActiveSheet.Shapes
shap.delete
Next shap
'saves the workbook
ActiveWorkbook.SaveAs myName
Application.DisplayAlerts = False
'Emails the workbook
Dim Recipients() As String
Dim n As Long
Dim i As Long

n = Sheet1.Range("a47").End(xlToRight).Column
ReDim Recipients(1 To n)

For i = 1 To n
Recipients(i) = Sheet1.Range("A" & i).Text
Next i

ActiveWorkbook.SendMail Recipients, "Shift Report"
Application.DisplayAlerts = True
ActiveWorkbook.Close

Thanks in advance,

wratpack
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
Hi wratpack,

I believe what SendMail is looking for in its Recipients argument is a variant containing an array, rather than the array itself. Try the following:

.
.
.
ReDim Recipients(1 To n)
Dim Recips As Variant

For i = 1 To n
Recipients(i) = Sheet1.Range("A" & i).Text
Next i

Recips = Recipients

ActiveWorkbook.SendMail Recipients:=Recips, "Shift Report"


Damon
 
Upvote 0

Forum statistics

Threads
1,214,605
Messages
6,120,476
Members
448,967
Latest member
visheshkotha

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top