Can I use VBA to activate a hyperlink in Outlook?
 

Hello,

I am relatively new to VBA and particularly to using it with Outlook. I
am using Outlook 2003 on XP.

Hoping someone can help ... I currently receive between 200 - 300
emails per day (this can increase to 600 at peak times) each of which
contains a hyperlink I need to activate and then print contents of the
window which opens from the hyperlink. I am trying to automate the
opening of the mail message, following the hyperlink, printing the
browser window, closing the browser, and moving to the next message to
repeat the actions until all items have been opened and printed. This
is a massively mundane and time consuming process to perform manually
and audit requirements demand the information be held in a hard copy
format.

I have been looking around the groups along with Dick's Clicks and
slipstick to see if I can learn to do this using VBA. I also have Macro
Scheduler v 6.0.0.14 which I can use for printing the browser window,
however, I cannot find how to activate the hyperlink from the Outlook
message item. For thos who have not heard of Macro Scheduler, it can be
programmed to perform cross application functions and scheduled in many
different ways to activate the developed macro and then repeat as
necessary.

Can anyone tell me if activating a hyperlink in Outlook using VBA is
even possible, please? I considered trying to export (or copy/paste)
the message text and/or the hyperlink to an Excel spreadsheet and
following the hyperlink from there but I get stuck again with how to
pull this information from the message and successfully load it into a
spreadsheet so the hyperlinks are all located in one column.. It's in a
standard message format with no form fields, however, the hyperlink
always appears immediately following a specific set of text within the
message which could help with locating the hyperlink within the message
body. The standard text is: "Please click here to view the details.:"

Unfortunately, the text which appears immediately before this sentence
can vary in length and content otherwise I would just copy it into
Excel then perform text-to-columns to load all the hyperlinks into the
one column for activating.

I would appreciate any guidance and advice, please. Is any of this
remotely possible? Can I do it in Outlook? Would a better solution be
to extract the message body text and/or hyperlink, stick it in Excel
then use VBA to run down a column of hyperlinks to activate browser
windows in order to have Macro Scheduler perform the print function?
How do I isolate the hyperlink if this is a better solution?

I am fortunate in having a spare PC which will be dedicated to this
function so I can continue to perform other tasks and be a great deal
more productive.

Any responses would be gratefully received.

TIA.

Wombat.

Can I use VBA to activate a hyperlink in Outlook?
 

Am 14 Jan 2006 23:01:17 -0800 schrieb :

I´ve read the first two paragraphs only. If I´m missing something important
then - please - point it out in a shorter way :-)

Exampel for a loop through all folder´s MailItems and moving them after
being handled:

Dim colItems as Outlook.MapiFolder
Dim obj as Object
Dim oMail as Outlook.MailItem
Dim i as Long
Dim oTargetFolder

With Application.Session.GetDefaultFolder(olFolderInbox )
Set colItems=.Items
' Assuming that the target folder is a subfolder of the Inbox
Set oTargetFolder = .Folders("TheFolderNameHere")
End With
For i=colItems.Count To 1 Step -1
Set obj=colItems(i)
If TypeOf obj is Outlook.MailItem Then
Set oMail = obj
' do the rest here
oMail.Move oTargetFolder
Endif
Next

Now you "just" need to find the hyperlink in the mail´s body. You can use
the InStr function for determining the link´s start and end, and then
extract it by the Mid function.

Add an UserForm to your project and the component "DHTMLEdit Control for IE
5". Drag that control onto the form. The control has a LoadURL method; in
the DocumentComplete event you can call the control´s Print method.

--
Viele Gruesse / Best regards
Michael Bauer - MVP Outlook



Hello,

I am relatively new to VBA and particularly to using it with Outlook. I
am using Outlook 2003 on XP.

Hoping someone can help ... I currently receive between 200 - 300
emails per day (this can increase to 600 at peak times) each of which
contains a hyperlink I need to activate and then print contents of the
window which opens from the hyperlink. I am trying to automate the
opening of the mail message, following the hyperlink, printing the
browser window, closing the browser, and moving to the next message to
repeat the actions until all items have been opened and printed. This
is a massively mundane and time consuming process to perform manually
and audit requirements demand the information be held in a hard copy
format.

I have been looking around the groups along with Dick's Clicks and
slipstick to see if I can learn to do this using VBA. I also have Macro
Scheduler v 6.0.0.14 which I can use for printing the browser window,
however, I cannot find how to activate the hyperlink from the Outlook
message item. For thos who have not heard of Macro Scheduler, it can be
programmed to perform cross application functions and scheduled in many
different ways to activate the developed macro and then repeat as
necessary.

Can anyone tell me if activating a hyperlink in Outlook using VBA is
even possible, please? I considered trying to export (or copy/paste)
the message text and/or the hyperlink to an Excel spreadsheet and
following the hyperlink from there but I get stuck again with how to
pull this information from the message and successfully load it into a
spreadsheet so the hyperlinks are all located in one column.. It's in a
standard message format with no form fields, however, the hyperlink
always appears immediately following a specific set of text within the
message which could help with locating the hyperlink within the message
body. The standard text is: "Please click here to view the details.:"

Unfortunately, the text which appears immediately before this sentence
can vary in length and content otherwise I would just copy it into
Excel then perform text-to-columns to load all the hyperlinks into the
one column for activating.

I would appreciate any guidance and advice, please. Is any of this
remotely possible? Can I do it in Outlook? Would a better solution be
to extract the message body text and/or hyperlink, stick it in Excel
then use VBA to run down a column of hyperlinks to activate browser
windows in order to have Macro Scheduler perform the print function?
How do I isolate the hyperlink if this is a better solution?

I am fortunate in having a spare PC which will be dedicated to this
function so I can continue to perform other tasks and be a great deal
more productive.

Any responses would be gratefully received.

TIA.

Wombat.

Can I use VBA to activate a hyperlink in Outlook?
 

Hello Michael,

My apologies for the length of the request. Maybe I should publish it
in hard copy :)

Thank you for the assistance. As I said, I'm pretty new to all of this
but I'll give it a go. I was getting stuck on how to locate the
hyperlink and you have provided what I need.

Thanks again.

Wombat.

Michael Bauer wrote:
Am 14 Jan 2006 23:01:17 -0800 schrieb :

I´ve read the first two paragraphs only. If I´m missing something important
then - please - point it out in a shorter way :-)

Exampel for a loop through all folder´s MailItems and moving them after
being handled:

Dim colItems as Outlook.MapiFolder
Dim obj as Object
Dim oMail as Outlook.MailItem
Dim i as Long
Dim oTargetFolder

With Application.Session.GetDefaultFolder(olFolderInbox )
Set colItems=.Items
' Assuming that the target folder is a subfolder of the Inbox
Set oTargetFolder = .Folders("TheFolderNameHere")
End With
For i=colItems.Count To 1 Step -1
Set obj=colItems(i)
If TypeOf obj is Outlook.MailItem Then
Set oMail = obj
' do the rest here
oMail.Move oTargetFolder
Endif
Next

Now you "just" need to find the hyperlink in the mail´s body. You can use
the InStr function for determining the link´s start and end, and then
extract it by the Mid function.

Add an UserForm to your project and the component "DHTMLEdit Control for IE
5". Drag that control onto the form. The control has a LoadURL method; in
the DocumentComplete event you can call the control´s Print method.

--
Viele Gruesse / Best regards
Michael Bauer - MVP Outlook



Hello,

I am relatively new to VBA and particularly to using it with Outlook. I
am using Outlook 2003 on XP.

Hoping someone can help ... I currently receive between 200 - 300
emails per day (this can increase to 600 at peak times) each of which
contains a hyperlink I need to activate and then print contents of the
window which opens from the hyperlink. I am trying to automate the
opening of the mail message, following the hyperlink, printing the
browser window, closing the browser, and moving to the next message to
repeat the actions until all items have been opened and printed. This
is a massively mundane and time consuming process to perform manually
and audit requirements demand the information be held in a hard copy
format.

I have been looking around the groups along with Dick's Clicks and
slipstick to see if I can learn to do this using VBA. I also have Macro
Scheduler v 6.0.0.14 which I can use for printing the browser window,
however, I cannot find how to activate the hyperlink from the Outlook
message item. For thos who have not heard of Macro Scheduler, it can be
programmed to perform cross application functions and scheduled in many
different ways to activate the developed macro and then repeat as
necessary.

Can anyone tell me if activating a hyperlink in Outlook using VBA is
even possible, please? I considered trying to export (or copy/paste)
the message text and/or the hyperlink to an Excel spreadsheet and
following the hyperlink from there but I get stuck again with how to
pull this information from the message and successfully load it into a
spreadsheet so the hyperlinks are all located in one column.. It's in a
standard message format with no form fields, however, the hyperlink
always appears immediately following a specific set of text within the
message which could help with locating the hyperlink within the message
body. The standard text is: "Please click here to view the details.:"

Unfortunately, the text which appears immediately before this sentence
can vary in length and content otherwise I would just copy it into
Excel then perform text-to-columns to load all the hyperlinks into the
one column for activating.

I would appreciate any guidance and advice, please. Is any of this
remotely possible? Can I do it in Outlook? Would a better solution be
to extract the message body text and/or hyperlink, stick it in Excel
then use VBA to run down a column of hyperlinks to activate browser
windows in order to have Macro Scheduler perform the print function?
How do I isolate the hyperlink if this is a better solution?

I am fortunate in having a spare PC which will be dedicated to this
function so I can continue to perform other tasks and be a great deal
more productive.

Any responses would be gratefully received.

TIA.

Wombat.