why this code opens two emails and reference isn't valid

H

hash993

New Member
Joined
Dec 18, 2022
Messages
11
Office Version
  1. 365
Platform
  1. Windows
Hello everyone,

this code is working properly"ish" the only issue when it opens outlook it opens two new emails not one.
then pop up " Reference isn't valid " then a second pop up "click row number #"

VBA Code: 
Sub addlinks()
    Dim lastrow As String
    
    lastrow = Cells(Rows.Count, 1).End(xlDown).Row

    For i = 3 To lastrow
        If Range("e" & i).Value <> "" Then

        
              ActiveSheet.Hyperlinks.Add Range("Z" & i), Address:="", SubAddress:="CreateEmailWithHTMLBody()", TextToDisplay:="Run Macro"
              
        End If
    Next
End Sub
Function CreateEmailWithHTMLBody()

    Dim objOutlook As Object
    Dim objMail As Object

    Set objOutlook = CreateObject("Outlook.Application")
    Set objMail = objOutlook.CreateItem(0)

    With objMail
        .To = "recipient@example.com"
        .Subject = "This is the subject" 

        'Set the HTML body of the email.
        .HTMLBody = "<html><body>This is the <b>HTML</b> body of the email.</body></html>"

        .Display 'Display the email before sending it.
    End With

    Set objMail = Nothing
    Set objOutlook = Nothing
    Exit Function
End Function
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
Sort by date Sort by votes
Not sure I follow. If you get "Reference isn't valid" how can the sub be called at all, let alone twice (I get it if I click on the hyperlink, so email code is not called at all)?
Using xlDown, doesn't lastrow become over a million for you? It does for me. Good thing I always step into other peoples code rather than just run it. I suggest you put a break point on the email sub start and step through and watch all of the flow to see what calls it a second time.
FWIW, you don't need Exit Sub there.
 
Upvote 0
Micron said:
Not sure I follow. If you get "Reference isn't valid" how can the sub be called at all, let alone twice (I get it if I click on the hyperlink, so email code is not called at all)?
Using xlDown, doesn't lastrow become over a million for you? It does for me. Good thing I always step into other peoples code rather than just run it. I suggest you put a break point on the email sub start and step through and watch all of the flow to see what calls it a second time.
FWIW, you don't need Exit Sub there.
I didn't think you could call a sub with a hyperlink?
Click to expand...
The code works just fine, the loop checks if there is data “in my case if there is email in column E” it will insert a hyperlink to send an email. If it’s empty it will stop.

But when i click the hyperlink it opens outlook new email twice. Then a popup “ saying reference isn’t valid “ when i click ok, another pop up saying “clicked row 3”.

What am trying to achieve is as below:
A link for each row to open email that contains some text and some values from cells from the same row. I have done using mailto formula/vba completely functioning but can’t seem to enter a table into it. So my next approach is email with html body.
 
Upvote 0
This seems to work for me
Address:="", SubAddress:=Range("M" & i).Address, TextToDisplay:="Run Macro"

VBA Code: 
Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
If Target.TextToDisplay = "Run Macro" Then CreateEmailWithHTMLBody
End Sub
Hyperlink points to its own cell. Your way, it points to my file location.
I have data up to row E15 in a test sheet. I still don't see why I get over 1 million for lastrow and you don't. Anyway maybe add the sub as I've shown and change the subaddress & see what you get. I get only 1 email.
 
Upvote 0
I'm not sure what the problem is but I would prefer to use event SelectionChange to run this macro rather than baking it into a hyperlink. I have never seen using a cell hyperlink to call a VBA Sub before.

Your code definitely sets lastrow to 1,048,576.
hash993 said:
If it’s empty it will stop.
Click to expand...
It doesn't stop. It iterates through all 1,048,576 rows and just skips the empty rows. You need to do this:
Rich (BB code): 
    lastrow = Cells(Rows.Count, 1).End(xlUp).Row
 
Upvote 0
6StringJazzer said:
I'm not sure what the problem is but I would prefer to use event SelectionChange to run this macro rather than baking it into a hyperlink. I have never seen using a cell hyperlink to call a VBA Sub before.

Your code definitely sets lastrow to 1,048,576.

It doesn't stop. It iterates through all 1,048,576 rows and just skips the empty rows. You need to do this:
Rich (BB code): 
    lastrow = Cells(Rows.Count, 1).End(xlUp).Row
Click to expand...
macrotest.PNG

without changing the code it only inserts hyperlink where column E is filled.
and below shows that it opens the outlook email twice
two emails.PNG


and below is the errors

reference.PNG
rowclicked.PNG
 
Upvote 0
You have other code you are not showing us, because neither your code nor Excel displays a message that says "Row x is clicked." This is probably coming from event handlers in the worksheet module such as SelectionChange. And the answer to your problem is likely in that code. Please show all your code.
 
Upvote 0
6StringJazzer said:
You have other code you are not showing us, because neither your code nor Excel displays a message that says "Row x is clicked." This is probably coming from event handlers in the worksheet module such as SelectionChange. And the answer to your problem is likely in that code. Please show all your code.
Click to expand...
full.PNG



this is the full code as shown in the beginning and its functioning. except its showing me the errors i took screenshot of.
 
Upvote 0
Posting code within code tags is preferred. Pictures are often too small to read code and no one can copy/paste to test.
You only get links where the other column is not blank as you say, BUT you are checking over a million rows with your way, so inefficient. Take out the IF and run it and you'll get over a million links.

If that's all the code you have in the whole project then I have no other suggestions, except I would take a look if you upload a copy somewhere.
 
Upvote 0
Micron said:
Posting code within code tags is preferred. Pictures are often too small to read code and no one can copy/paste to test.
You only get links where the other column is not blank as you say, BUT you are checking over a million rows with your way, so inefficient. Take out the IF and run it and you'll get over a million links.

If that's all the code you have in the whole project then I have no other suggestions, except I would take a look if you upload a copy somewhere.
Click to expand...
The code is posted in the first post exactly the same.
 
Upvote 0
You must log in or register to reply here.

Similar threads

larinda4
  • Question
VBA: Send Outlook Email & Attach PDF
Replies
1
Views
105
Trebor76
T
J
  • Question
Parse HTML table or extract Outlook body items to excel sheet
Replies
2
Views
207
Jaffirahamed
J
G
  • Question
Excel VBA to reply to existing email chain and add workbook as attachment
Replies
4
Views
193
gopalgriffith
G
A
  • Question
VBA run-time error
Replies
2
Views
272
Aaro23
A
M
  • Question
VBA for sending all emails in outlook draft folder
Replies
8
Views
838
Moustafa Houssien
M

Forum statistics

Threads
1,214,653
Messages
6,120,750
Members
448,989
Latest member
mariah3

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top