Macro to send email from Vlookup cell value [Solved/Closed]
KingTut -
May 19, 2010 at 09:02 AM - Latest reply: rizvisa14481
Posts Thursday January 28, 2010Registration dateContributorStatus
January 6, 2016
Last seen
- May 19, 2010 at 11:36 AM
I am trying to create a macro that will among other things send an email to each address in a cell. The only problem is that the cell with the email addy contains a Vlookup formula and not the actual address. So I am wondering what I need to change in the If statement to make vb recognize or translate the vlookup into text so that the macro will send. Any help would be greatly appreciated!!
Private Sub CommandButton1_Click()
Dim OutApp As Object
Dim OutMail As Object
Dim cell As Range
Application.ScreenUpdating = False
Set OutApp = CreateObject("Outlook.Application")
On Error GoTo cleanup
ThisWorkbook.Save
For Each cell In Columns("I").Cells.SpecialCells(xlCellTypeConstants)
If cell.Value Like "?*@?*.?*" Then
Set OutMail = OutApp.CreateItem(0)
On Error Resume Next
With OutMail
.To = cell.Value
.CC = "email address"
.Subject = "I/C Memo"
.Body = "Hello " & Cells(cell.Row, "G") & "," _
& vbNewLine & vbNewLine & _
"I have booked " & "$" & Cells(cell.Row, "C") _
& " to Division " & C & ThisWorkbook.Sheets("Sheet2").Range("B5").Value & " " & "for " & ThisWorkbook.Sheets("Sheet2").Range("C9").Value & "." & vbNewLine & _
"Please also book this amount to your " & ThisWorkbook.Sheets("Sheet2").Range("B6").Value & "." _
& vbNewLine & vbNewLine & _
ThisWorkbook.Sheets("Sheet2").Range("C2").Value & vbNewLine & _
"Capabilities Accounting" & vbNewLine & _
ThisWorkbook.Sheets("Sheet2").Range("C3").Value
.Attachments.Add ActiveWorkbook.FullName
.ReadReceiptRequested = True
.Send
End With
On Error GoTo 0
Set OutMail = Nothing
End If
Next cell
cleanup:
Set OutApp = Nothing
Application.ScreenUpdating = True
End Sub
That was it!! I had been banging my head against the wall for 30 mins...
Such a simple error grrrr....
One more question that maybe you could answer for me?? If I want to return a value in a cell that has been formatted in the body of my message is there a certain naming convention to use?
i.e.- I have a cell with 25455.43 in it formatted to show $25,455.43.
Using the line ThisWorkbook.Sheets("Sheet2").Range("B5").Value only seems to input the original number without formatting. Is there a way to correct this easily?
has helped 1676 users this month
Such a simple error grrrr....
One more question that maybe you could answer for me?? If I want to return a value in a cell that has been formatted in the body of my message is there a certain naming convention to use?
i.e.- I have a cell with 25455.43 in it formatted to show $25,455.43.
Using the line ThisWorkbook.Sheets("Sheet2").Range("B5").Value only seems to input the original number without formatting. Is there a way to correct this easily?
"I have booked " & Cells(cell.Row, "D") _
That should have it read: I have booked $254,455.43
How do I use the .text function there?
"I have booked " & Cells(cell.Row, "D").text is how you would code it