PDA

View Full Version : EMAIL command button



hillaryruth
11-13-2007, 10:45 AM
I have a command button on a spread sheet to send the sheet by email. It works fine, but I can't figure out how to add a second recipient email address to the first and make an email go to both. I want to add
James.Brown@florida.gov

Would someone please show me how to do this


Private Sub CommandButton1_Click()
Application.Dialogs(xlDialogSendMail).Show "Alicia.Brown@florida.gov", "Check Register", True
'xlDialogSendMail recipients, subject, return_receipt
End Sub

figment
11-13-2007, 10:52 AM
if your using outlook this will work otherwise you will have to find what devider you mail program uses between names

Private Sub CommandButton1_Click()

Application.Dialogs(xlDialogSendMail).Show "Alicia.Brown@florida.gov" & " ; " & "bla@gmail.com", "Check Register", True
'xlDialogSendMail recipients, subject, return_receipt
End Sub


or if you have an aray of names you could do something like this

Private Sub CommandButton1_Click()

Application.Dialogs(xlDialogSendMail).Show makeaddresslist(aray_of_addresses), "Check Register", True
'xlDialogSendMail recipients, subject, return_receipt
End Sub


Function makeaddresslist(a As Variant)
Dim b As Long
Dim c As String
c = ""
For b = LBound(a) To UBound(a)
If c = "" Then
c = a(b)
Else
c = c & " ; " & a(b)
End If
Next
makeaddresslist = c
End Function

hillaryruth
11-13-2007, 01:27 PM
I keep getting a popup box in Outlook. "Check Names" "Microsoft Outlook does not recognize Alicia.Rogers@florida.gov;mike.hughes@florida.gov
and it asks me to select the address to use.

Any ideas


Private Sub CommandButton1_Click()

Application.Dialogs(xlDialogSendMail).Show "Alicia.Rogers@florida.gov" & " ; " & "mike.hughes@florida.gov", "Check Register", True
'xlDialogSendMail recipients, subject, return_receipt
End Sub

figment
11-13-2007, 01:43 PM
looks like your runing into a limitation of the xlDialogSendMail call. you could try sending the message twice once to each address. or try using the Outlook.Application.CreateItem(olMailItem) object.

hillaryruth
11-13-2007, 01:46 PM
I'll need to look this up... I don't know what it is-- Thanks
Outlook.Application.CreateItem(olMailItem) object

Dr.K
11-13-2007, 01:58 PM
There are MANY ways to send email, some better then others. I tend to use the Outlook Objcet model, becuase everyone at the company I work for has Outlook, and becuase Outlook tends to respond well to late-binding (which helps when you've got many users on older versions of Outlook).

Here is a great resource for different ways of sending mail from VBA:
http://www.rondebruin.nl/sendmail.htm


Here is some code I used to send whole workbooks:

Dim OutApp As Object
Dim OutMail As Object


'Send workbook via email
Application.StatusBar = "Sending email... Please wait, this could take a minute."
Set OutApp = CreateObject("Outlook.Application")
OutApp.Session.Logon
Set OutMail = OutApp.CreateItem(0)
On Error Resume Next
With OutMail
.To = eSubAddress
.CC = ""
.BCC = ""
.Subject = "AUTO Performance Request: " & Left(FName, Len(FName) - 4)

.Attachments.Add ActPath & FName

On Error GoTo EmailErr
.Send
On Error GoTo GenErr

End With

Set OutMail = Nothing
Set OutApp = Nothing


Hope that helps!