• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Importing outlook emails to excel in 2013 version.

Monty

Monty

Well-Known Member
Hello everybody.

Still waiting for some reply..tried all the ways.

Below attached file works perfectly with home desktop as you know outlook is connected to SMTP/POP server...but the same code does not work in my office as outlook 2013 is connected exchange server..

Error at : Cells(n, 3) = olMail.SenderEmailAddress

only when running this code in my office...else working fine at home.

Am not really sure if that is problem or something else...please help
 

Attachments

  • OutLook Emails.xlsb
    18.2 KB · Views: 12
Hello Monty.

Hope you are doing good..i become big fan of you.

i just checked your file and working fine...but still trying to understand why importing mails to excel is different when outlook 2013 is connected to exchange server...let me check if i can get some clue.

Yours Arpana.
 
Hello Arpana.

Iam waiting for response from our experts why it is different?

only error @ when getting Sender's Email id only..

Thanks for responding.
 
Monty

Cross posting is good but always mention the links in both the threads.

http://stackoverflow.com/questions/...look-2013-with-exchange-server-with-excel-vba

Just tested with Excel/Outlook2013 connected to Exchange Server & didn't replicate the issue.

Are u getting error on very first email or in a specific email??

However Check with below changes.

Code: 
Cells(n, 3) = Get_Sender_Address(olMail) 'olMail.SenderEmailAddress

Code: 
Function Get_Sender_Address(oMsg As Outlook.MailItem) As String
Dim oExUser As Outlook.ExchangeUser, oAddType As Outlook.AddressEntry

Set oAddType = oMsg.Sender

If oAddType.AddressEntryUserType = olExchangeUserAddressEntry Then
    Set oExUser = oAddType.GetExchangeUser
        Get_Sender_Address = oExUser.PrimarySmtpAddress
Else
    Get_Sender_Address = oMsg.SenderEmailAddress
End If

Set oAddType = Nothing:    Set oExUser = Nothing

End Function
 
Hello Deepak....Thank u so very much...Still no luck...Error are If oAddType.AddressEntryUserType = olExchangeUserAddressEntry Then
Object doesn't support..
Getting error at first Email.
.
Please Help..
 
Change the UDF & check!

Code: 
Function Get_Sender_Address(Item As Outlook.MailItem) As String
Dim strAddress As String

On Error Resume Next 'PropertyAccessor can raise an exception if a property is not found
If Item.SenderEmailType = "SMTP" Then
  strAddress = Item.SenderEmailAddress
Else
  'read PidTagSenderSmtpAddress
  strAddress = Item.PropertyAccessor.GetProperty("http://schemas.microsoft.com/mapi/proptag/0x5D01001F")
  If Len(strAddress) = 0 Then
    Set objSender = Item.Sender
    If Not (objSender Is Nothing) Then
      'read PR_SMTP_ADDRESS_W
      strAddress = objSender.PropertyAccessor.GetProperty("http://schemas.microsoft.com/mapi/proptag/0x39FE001F")
      If Len(strAddress) = 0 Then
        'last resort
        Set exUser = objSender.GetExchangeUser
        If Not (exUser Is Nothing) Then
          strAddress = exUser.PrimarySmtpAddress
        End If
      End If
    End If
  End If
End If
Get_Sender_Address = strAddress
End Function

Ref: http://stackoverflow.com/questions/34922075/get-senders-email-address-in-excel-vba-from-outlook

Modified : Few things...
 
Last edited:
R u getting the below error then you are not connected to Exchange server while running the code!

Capture.JPG

Error.png


Check with below code that what value u are getting, i am getting '700' & you might 400/300

Code: 
Sub ExchangeConnectionValue()
'https://msdn.microsoft.com/en-us/library/office/ff868474(v=office.14).aspx
Dim olApp As New Outlook.Application, ExchangeStatus  As OlExchangeConnectionMode

ExchangeStatus = olApp.GetNamespace("MAPI").ExchangeConnectionMode

MsgBox ExchangeStatus
End Sub
 
Hello Deepak....Am getting 700 when comnected to exchange server and 400 not connected at my office...and version we are using 2013excel and outlook..


Pls Help.
 
Last edited:
Hello Deepak...there is no error...just showing the number when running piece of code u have provided.
 
hello Deepak.


i used the functionu have provided....no error
strAddress variable shows empty....

i think we are almost there...Pls help.
 
You must log in or register to reply here.
Back
Top