send mail

[hyperionaxa]

Hi, I am tring to create something on access 2003 to send external mail to
the suppliers and I am using the following code:

Private Sub CommandButton1_Click()
'Sub Mail_Selection_Range_Outlook_Body()
' Don't forget to copy the function RangetoHTML in the module.
' Working in Office 2000-2007
Dim rng As Range
Dim OutApp As Object
Dim OutMail As Object
Dim strbody As String

Set rng = Nothing
On Error Resume Next
'Only the visible cells in the selection
'Set rng = Selection.SpecialCells(xlCellTypeVisible)
'You can also use a range if you want
Set rng = Sheets("SHEETNAME").Range("a1:b18").SpecialCells
(xlCellTypeVisible)
On Error GoTo 0

If rng Is Nothing Then
MsgBox "The selection is not a range or the sheet is protected" & _
vbNewLine & "please correct and try again.", vbOKOnly
Exit Sub
End If

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

Set OutApp = CreateObject("Outlook.Application")
OutApp.Session.Logon
Set OutMail = OutApp.CreateItem(0)

On Error Resume Next
With OutMail
.To = "(e-mail address removed)"
'.CC = ""
'.BCC = ""
.Subject = "Report"
strbody = "xyz" & "<br>" & _
"" & "<br>" & _
"Thanks" & "<br><br><br>"
.HTMLBody = strbody & RangetoHTML(rng)
.Send 'or use .Display
End With
On Error GoTo 0

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

Set OutMail = Nothing
Set OutApp = Nothing
End Sub

But, the issue is that, users would like to input/change the email address
for the emails to be sent. CAN SOMEONE PLEASE HELP ME WITH THIS.

Thanks in advance.

 

[Evi]

Have a textbox in your form with button and replace the (e-mail address removed) with a
variable

add a declaration to your 'Dim' section

Dim MyEmailAddy as Hyperlink

MyEmailAddy = Me.Text1 (or whatever you decide to call the text box)

then change the email line to

.To = MyEmailAddy

Evi