MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Activating the hyperlink for an eMail with VBA

  Asked By: Sunil    Date: Aug 19    Category: MS Office    Views: 1125

I have a list of 5000+ eMail addresses in a column. I need to activate
each of the hyper links in a loop. The user needs to be able to click
on the cell and OutLook opens to send an eMail.

I just need the code to do the following:


Would I use SendKeys? or is there a faster/better way?



1 Answer Found

Answer #1    Answered By: Zachary Bouchard     Answered On: Aug 19

Couldn't get SendKeys to work so I opted for the following Sub

Sub eMailActivation()
CurrentRow = 4 'after the header
EndOfFile = Cells(Rows.Count, "A").End(xlUp).Row 'get EndOfFile

While CurrentRow < EndOfFile + 1
Cells(CurrentRow, 6).Select 'Column F
eMailAddress = ActiveCell.Value
If eMailAddress <> Empty Then
ActiveSheet.Hyperlinks.Add _
Anchor:=Selection, _
Address:="mailto:" & eMailAddress, _
End If
CurrentRow = CurrentRow + 1
End Sub

Didn't find what you were looking for? Find more on Activating the hyperlink for an eMail with VBA Or get search suggestion and latest updates.