sendmail for user typed email addresses

tidz123

New Member
Joined
Mar 11, 2010
Messages
6
how can i use sendmail where i want the user to type the email address they wish to send the workbook to?

if i do it via a form, what will the code be for the send button as the sendmail code i have at the moment is the one where you have to type in the email addresses in the actual code itself. this does not allow the user to send it to who ever they want to send it to.

all help needed and appreciated :cool:

thanks
 

Some videos you may like

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

6StringJazzer

Well-known Member
Joined
Jan 27, 2010
Messages
542
Office Version
2019
Platform
Windows
It would be very helpful if you posted the sendmail code you have.

Without that, I'll recommend the following code, which sends mail via Outlook. Note that it assumes that you have a button called buttonOK, and a text box containing the Send To email address called textboxSendTo. I have used the mail portion of this code before but for this example I did not actually create a full-blown solution so let me know if you have issues integrating this to your own form/code. Also, the content of the textbox should be validated to ensure it's valid email address syntax.

An alternative would be to just prompt for an email address using InputBox instead of putting up a form.

Code:
Private Sub buttonOK_Click()
 
   Dim recipient As String
   recipient = textboxSendTo.Value
 
   Dim subject As String
   subject = "Put your subject line here"
 
   Dim body As String
   body = "Put the body of the message here"
 
   Dim olMail As MailItem
   Set olMail = Outlook.Application.CreateItem(olMailItem)
 
   With olMail
       .recipients.Add (recipient)
       .subject = subject
       .Importance = olImportanceNormal
       .body = body
   End With
 
   olMail.Send
 
 
End Sub
 

tidz123

New Member
Joined
Mar 11, 2010
Messages
6
Well I have the following code which im using on a macro, but as i need the user to type in the email addresses i thought it would be more efficient if it was on a form?

Sub Mail_Workbook()
Dim wb As Workbook
Set wb = ActiveWorkbook

If Val(Application.Version) >= 12 Then
If wb.FileFormat = 51 And wb.HasVBProject = True Then
MsgBox "There is VBA code in this xlsx file, there will" & vbNewLine & _
"be no VBA code in the file you send. Save the" & vbNewLine & _
"file first as xlsm and then try the macro again.", vbInformation
Exit Sub
End If
End If

On Error Resume Next
wb.SendMail Array("email@one.com", "email@two.com"), _
"Updated Weekly Sheet 2"
On Error GoTo 0
End Sub
 

tidz123

New Member
Joined
Mar 11, 2010
Messages
6
also if i used the code you typed before, would it attach the activeworksheet automatically??

thanks
 

Ron de Bruin

Board Regular
Joined
Aug 1, 2006
Messages
234
Platform
Windows, MacOS
Hi

See this tip page for all my SendMail examples
http://www.rondebruin.nl/mail/tips1.htm

use "" and the mail will popup and the use can type or use his addressbook

If you use the Outook object model code from my site you
can use .Display instead of .Send
 

6StringJazzer

Well-known Member
Joined
Jan 27, 2010
Messages
542
Office Version
2019
Platform
Windows
I think your best option is to simply use an InputBox with the code you already have, assuming you already have that code doing what you want.
Code:
wb.SendMail Array(InputBox "Enter first email address", _
                  InputBox "Enter second email address"), _
            "Updated Weekly Sheet 2"
 

Watch MrExcel Video

Forum statistics

Threads
1,099,776
Messages
5,470,713
Members
406,718
Latest member
waseem11

This Week's Hot Topics

Top