Hi I have this code from another excel file,
How can i add this code to the the userform "ContactForm" for the file Contact list for Invoicing-Demo2.xls which I am attaching it.
Here it's the code I would like to add the above so I can double click on the customer and send an email:
Thanks
How can i add this code to the the userform "ContactForm" for the file Contact list for Invoicing-Demo2.xls which I am attaching it.
Here it's the code I would like to add the above so I can double click on the customer and send an email:
Code:
Private Sub ListBox1_Click()
Dim Customer As Variant
Dim Name As String
Dim firstaddress As String
Dim rng As Range
Customer = Empty
'If you add more than 500 names you will need to increase this
With ActiveSheet.Range("a2:e1000")
Name = ListBox1.Value
Set Customer = .Find(What:=Name, LookIn:=xlValues)
If Not Customer Is Nothing Then Customer.Rows.EntireRow.Select Else Exit Sub
End With
Set rng = Nothing
On Error Resume Next
' Only send the visible cells in the selection.
Set rng = Selection.SpecialCells(xlCellTypeVisible)
' You can also use a range with the following statement.
Set rng = Sheets("Statement").Range("A3:I29").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
'closes the form when you click on a name
' Unload Me
Dim ce As Range, i As Long
Dim OutApp As Object
Dim OutMail As Object
Dim strto As String, strcc As String, strbcc As String
Dim strsub As String, StrBody As String
Dim wksht As Worksheet
Dim rw As Integer
Set OutApp = CreateObject("Outlook.Application")
OutApp.Session.Logon
Set OutMail = OutApp.CreateItem(0)
Set wksht = Worksheets("ContactsCollection")
rw = ActiveCell.Row
strto = wksht.Cells(rw, "c").Value & ";" & wksht.Cells(rw, "J").Value
strcc = "ar@hyperwallet.com" & ";" & "DLeung@hyperwallet.com" & ";" & wksht.Cells(rw, "K").Value
strbcc = ""
strsub = wksht.Cells(rw, "D").Value
StrBody = "Hi" & " " & wksht.Cells(rw, "b").Value & "," & "<br>" & "<br>" & wksht.Cells(rw, "H").Value
'"Account Delinquent"
'wksht.Cells(rw, "D").Value
'StrBody = "Hi" & " " & wksht.Cells(rw, "b").Value & ", " & vbCrLf & vbCrLf & "" & wksht.Cells(rw, "H").Value
'End With
With OutMail
.To = strto
.CC = strcc
.BCC = strbcc
.Subject = strsub
.HTMLBody = StrBody & vbCrLf & RangetoHTML(rng) '.Body = StrBody
.display
End With
Set OutMail = Nothing
Set OutApp = Nothing
Set Customer = Nothing
With UserForm1
Unload Me
End With
[a1].Select
End Sub
Thanks