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
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!
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.