how do I add multiple email address?

  • Thread starter ryan.fitzpatrick3
  • Start date
R

ryan.fitzpatrick3

I have this code where it works fine, but I want to add multiple email
addresses.

Sub Email_Everyone()
'Working in 2000-2007
Dim nResult As Long
nResult = MsgBox( _
Prompt:="Do you want to mail?", _
Buttons:=vbYesNo)
If nResult = vbYes Then
'Rest of macro macro

End If
If nResult = vbNo Then
Exit Sub
End If


Dim Source As Range
Dim Dest As Workbook
Dim wb As Workbook
Dim TempFilePath As String
Dim TempFileName As String
Dim FileExtStr As String
Dim FileFormatNum As Long

Set Source = Nothing
On Error Resume Next
Set Source = Range("Everything").SpecialCells(xlCellTypeVisible)
On Error GoTo 0

If Source Is Nothing Then
MsgBox "The source is not a range or the sheet is protected,
please correct and try again.", vbOKOnly
Exit Sub
End If

With Application
.ScreenUpdating = False
.EnableEvents = False
End With

Set wb = ActiveWorkbook
Set Dest = Workbooks.Add(xlWBATWorksheet)

Source.Copy
With Dest.Sheets(1)
.Cells(1).PasteSpecial Paste:=8
.Cells(1).PasteSpecial Paste:=xlPasteValues
.Cells(1).PasteSpecial Paste:=xlPasteFormats
.Cells(1).Select
Application.CutCopyMode = False
Cells.Select
Cells.EntireColumn.AutoFit
ActiveWindow.DisplayGridlines = False
ActiveWindow.Zoom = 85
End With

TempFilePath = Environ$("temp") & "\"
TempFileName = "Selection of " & wb.Name & " " & Format(Now, "dd-
mmm-yy h-mm-ss")

If Val(Application.Version) < 12 Then
'You use Excel 2000-2003
FileExtStr = ".xls": FileFormatNum = -4143
Else
'You use Excel 2007
FileExtStr = ".xlsx": FileFormatNum = 51
End If

With Dest
.SaveAs TempFilePath & TempFileName & FileExtStr,
FileFormat:=FileFormatNum
On Error Resume Next
.SendMail "(e-mail address removed)", _
"Data"
On Error GoTo 0
.Close SaveChanges:=False
End With

Kill TempFilePath & TempFileName & FileExtStr

With Application
.ScreenUpdating = True
.EnableEvents = True
End With


End Sub


I need help on the .sendmail part. What should I do?

ryan
 
D

dustinbrearton via OfficeKB.com

Depeding on what email client you are using you should just be able to
seperate the email addresses with comma's.

Example:
..SendMail "(e-mail address removed), (e-mail address removed), (e-mail address removed)"
 
R

ryan.fitzpatrick3

Thanks, I've seen your page before, but I didn't see an example with
multiple email address.
 
R

ryan.fitzpatrick3

Maybe there is something wrong with my code, I put in the array as
your page indicates and when I run the code, it prompts me, in which I
click yes, then it sends, but doesn't go to my email address. I use
Microsoft outlook.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top