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
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