Extracting Mail from Outlook into Excel

robw

Board Regular
Joined
Dec 18, 2002
Messages
161
Hi
I have recently moved to a new PC (windows 10 home, Excel 2016) from my old PC ( W10 Pro, Excel 2016) and have found that part of one of my Excel workbooks no longer works as expected.

The workbook that includes the following VBA :-

Sub get_mail()

Dim olApp As Outlook.Application
Dim olNs As Namespace
Dim Fldr As MAPIFolder
Dim olMail As Variant

Set olApp = New Outlook.Application
Set olNs = olApp.GetNamespace("MAPI")
Set Fldr = olNs.GetDefaultFolder(olFolderInbox)

For Each olMail In Fldr.Items

‘ ** Extract stuff from olMail.Subject and olMail.Body into excel worksheet**
:
:
Next olMail
End Sub

This used to work on my old machine and correctly extracted information from mail items in my current Outlook Inbox. However, on the new machine I get a problem at the "Set Fldr" line. It pops up a small dialogue which asks me to Create New Profile. I didn't used to get this. Why do I need a new profile? Why isn't it picking up my Outlook profile?.... I suspect there is something wrong in my environment, but what am I doing wrong?

Is there anything I can do to better understand what is going on?

Any help gratefully accepted
Thanks
Rob
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
a user yesterday discovered they didn't have .NET installed which caused them code failure on one machine. It may not be the answer but installed VBA references and the such like often hold a key
 
Upvote 0
a user yesterday discovered they didn't have .NET installed which caused them code failure on one machine. It may not be the answer but installed VBA references and the such like often hold a key

Thanks mole999....I;ve googled how to check that .NET is installed and this led use the command...
reg query "HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\NET Framework Setup\NDP"

This appears to indicate that I do have .NET installed. So, sadly, I don't think that's my problem

Many Thanks for your reply though

Any other thoughts please.....anyone?
 
Upvote 0
silly question, since you have moved, have you been using outlook regularly? is outlook enabled for macros ?

clutching at straws

is the old machine available to look at vba references
 
Upvote 0
silly question, since you have moved, have you been using outlook regularly? is outlook enabled for macros ?

clutching at straws

is the old machine available to look at vba references


Hi mole999

Unfortunately my old machine no longer has office 2016 on it as I have moved the licence to the new machine.
I have made some progress though. I played along with the “Create New Profile” prompt and created an Outlook profile called “Rob” and pointed it at my ISP mail server. I can now successfully access my Outlook files from my excel worksheet.
I am still a bit puzzled why I had to create this Outlook profile though and why excel didn’t pick it up automatically. The odd thing is that when I open Outlook normally (eg from the Taskbar) it opens and shows I have exactly one profile, called “Outlook” (the one I originally set up soon after installing Office 2016). When I intercept the Outlook that is opened while my excel macro is running (via the icon in the tray) , it shows I have exactly one profile called “Rob” (setup as described above) . Neither “version” of outlook can see the profile in the other.
( a day later…)
Looking further I appear to have somehow got two versions of Outlook 2016 loaded. Two icons in the Start Menu. Both look the same but one opens the Outlook with the Outlook profile and the other the Outlook with the Rob profile. Similarly I have 2 Excels and two Words etc, but these appear identical in both cases. Think this may have come about as there was a trial version of Office 365 on the new machine and I loaded a HUP version of Office 2016 on top, expecting Microsoft to sort it out in the wash….obviously not. I think I need to uninstall/reinstall a few things
Thanks for your help
Cheers
Rob
 
Upvote 0

Forum statistics

Threads
1,213,557
Messages
6,114,291
Members
448,564
Latest member
ED38

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