Hi can anyone help resolve why, when i run this code to get my emails into excel, it works to list the emails, but then when I click on the hyperlinks to open the emails, it takes me off to one drive instead of allowing me to open the outlook email?
Option Explicit
Sub ImportEmails()
Dim olApp As Object ' Outlook.Application
Dim olNamespace As Object ' Outlook.Namespace
Dim olFolder As Object ' Outlook.MAPIFolder
Dim olMailItem As Object ' Outlook.MailItem
Dim olExplorer As Object ' Outlook.Explorer
Dim olSelection As Object ' Outlook.Selection
Dim rng As Range
Dim row As Integer
' Set the reference to Outlook Application
On Error Resume Next
Set olApp = GetObject(, "Outlook.Application")
If olApp Is Nothing Then
Set olApp = CreateObject("Outlook.Application")
End If
On Error GoTo 0
' Select the specified Outlook folder
Set olNamespace = olApp.GetNamespace("MAPI")
Set olFolder = olNamespace.PickFolder
If Not olFolder Is Nothing Then
' Set reference to the active sheet and the range to paste the hyperlinks
Set rng = Sheet3.Range("A2")
row = 2
' Loop through each email in the selected folder
For Each olMailItem In olFolder.Items
' Check if the item is a MailItem
If olMailItem.Class = 43 Then
' Add the hyperlink to the email in the specified range
rng.Hyperlinks.Add rng, olMailItem.entryID, , , olMailItem.Subject
row = row + 1
Set rng = rng.Offset(1, 0)
End If
Next olMailItem
' Select the range with hyperlinks
Sheet3.Activate
Sheet3.Range("A2:A" & row - 1).Select
End If
' Clean up objects
Set olExplorer = Nothing
Set olSelection = Nothing
Set olFolder = Nothing
Set olNamespace = Nothing
Set olApp = Nothing
End Sub
Option Explicit
Sub ImportEmails()
Dim olApp As Object ' Outlook.Application
Dim olNamespace As Object ' Outlook.Namespace
Dim olFolder As Object ' Outlook.MAPIFolder
Dim olMailItem As Object ' Outlook.MailItem
Dim olExplorer As Object ' Outlook.Explorer
Dim olSelection As Object ' Outlook.Selection
Dim rng As Range
Dim row As Integer
' Set the reference to Outlook Application
On Error Resume Next
Set olApp = GetObject(, "Outlook.Application")
If olApp Is Nothing Then
Set olApp = CreateObject("Outlook.Application")
End If
On Error GoTo 0
' Select the specified Outlook folder
Set olNamespace = olApp.GetNamespace("MAPI")
Set olFolder = olNamespace.PickFolder
If Not olFolder Is Nothing Then
' Set reference to the active sheet and the range to paste the hyperlinks
Set rng = Sheet3.Range("A2")
row = 2
' Loop through each email in the selected folder
For Each olMailItem In olFolder.Items
' Check if the item is a MailItem
If olMailItem.Class = 43 Then
' Add the hyperlink to the email in the specified range
rng.Hyperlinks.Add rng, olMailItem.entryID, , , olMailItem.Subject
row = row + 1
Set rng = rng.Offset(1, 0)
End If
Next olMailItem
' Select the range with hyperlinks
Sheet3.Activate
Sheet3.Range("A2:A" & row - 1).Select
End If
' Clean up objects
Set olExplorer = Nothing
Set olSelection = Nothing
Set olFolder = Nothing
Set olNamespace = Nothing
Set olApp = Nothing
End Sub