Macro to send email from Vlookup cell value

Solved/Closed
KingTut - May 19, 2010 at 09:02 AM
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - May 19, 2010 at 11:36 AM
Hello,

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

1 response

rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
May 19, 2010 at 09:23 AM
If this vlookup is in column I, then you are saying so yourself
Columns("I").Cells.SpecialCells(xlCellTypeConstants)

Shouldn't you be using xlCellTypeFormulas?
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?
0
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
May 19, 2010 at 10:41 AM
In that case use .Text instead of .Value
0
Sorry I pasted the wrong line

"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?
0
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
May 19, 2010 at 11:36 AM
Same thing. when you say nothing, .value is implied

"I have booked " & Cells(cell.Row, "D").text is how you would code it
0