sendmail for user typed email addresses

[tidz123]

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

thanks

 

[jeffreybrown]

Take a look here http://www.rondebruin.nl/sendmail.htm

 

[6StringJazzer]

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.

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]

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]

also if i used the code you typed before, would it attach the activeworksheet automatically??

thanks

 

[Ron de Bruin]

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]

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.

wb.SendMail Array(InputBox "Enter first email address", _
                  InputBox "Enter second email address"), _
            "Updated Weekly Sheet 2"