Menu
  1. Accueil
  2. Tech Forum
  3. Office Software
  4. Excel

Macro to send email from Vlookup cell value [Solved/Closed]

Report
-
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
 -
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
Related:

1 reply

Answer 1 / 1
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
 765
If this vlookup is in column I, then you are saying so yourself
Columns("I").Cells.SpecialCells(xlCellTypeConstants)

Shouldn't you be using xlCellTypeFormulas?
2
Thank you
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2942 users have said thank you to us this month

Report

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?
Report
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
 765
In that case use .Text instead of .Value
Report

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?
Report
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
 765
Same thing. when you say nothing, .value is implied

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

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!