none
VBA Copy to Excel 2013 creating extra strings RRS feed

  • Question

  • I have a piece of code that runs in outlook. The code runs through the body of email and copies specific words into Excel cells.

    The code works just fine in Office 2010, but when i use the code in Office 2013 the words has extra strings copied to excel cells.

    Private Sub deffolder_Click()
    Unload Me
        Dim olapp As Outlook.Application
        Dim oAccount As Outlook.Account
        Dim fqdn() As String, host() As String, server() As String, y As Long
        Dim si() As String, ar() As String, ur() As String, emoc As String
        Dim xlapp As Object ' Excel.Application
        Dim xlwkb As Object ' Excel.Workbook
        Dim folder As Outlook.MAPIFolder, ns As Outlook.NameSpace, tempfol
        Dim item As Object
        ReDim Preserve ar(n)
        ReDim Preserve ur(n)
        Dim trigger As String
        n = 0
        X = 0
        Set ns = GetNamespace("MAPI")
    
    For Each oAccount In Application.Session.Accounts
        If oAccount = "example@email.com" Then
        Set folder = oAccount.DeliveryStore.GetDefaultFolder(olFolderInbox)
    start:
    If folder.Items.Count > 0 Then
        MsgBox "Copying Servers from emails..", vbInformation, "Info"
        Set xlapp = CreateObject("Excel.Application") ' New Excel.Application
        Set xlwkb = xlapp.Workbooks.Add
        For Each item In folder.Items
        'Set Sender = item.Sender
            If item.Subject Like "test" And item.Sender Like "Tested*" Then
                fqdn() = Split(Replace(item.body, "VM IP", "VM Name: "), "VM Name: ")
                fqdn(1) = Replace(fqdn(1), vbNewLine, vbNullString)
                X = X + 1
                'Writing Values in Excel Sheet for Servers from Cloud Emails
                xlapp.Cells(X, "A") = fqdn(1)                
                xlapp.Cells.wraptext = False
            End If
    End if
      End if
    Next

    The cell value in excel has "expected output" and "*" and "tabspace" included.

    By using fqdn(1) = Replace(fqdn(1), "", vbNullString), i'm able to replace the "astriex" but unable to replace the "tab space" using same method replace(fqdn(1), "     ", vbnullstring).

    And in the first place what has caused issue in "office 2013", I wonder.

    Any Suggestions/ideas?


    Friday, January 8, 2016 7:22 AM

All replies

  • Hello Karthik,

    Did you have a chance to look at the HTML markup of Outlook messages? Are there any difference?

    Anyway, you use the Split function to remove additional whitespace, if any. Or just use the Word object model instead. 

    The Outlook object model provides three main ways for working with item bodies:

    1. Body - a string representing the clear-text body of the Outlook item. 
    2. HTMLBody - a string representing the HTML body of the specified item.
    3. Word editor - the Microsoft Word Document Object Model of the message being displayed. The WordEditor property of the Inspector class returns an instance of the Document class from the Word object model which you can use to set up the message body.

    You can read more about all these ways in the Chapter 17: Working with Item Bodies. It us up to you which way is to choose to deal with the message body.

    Friday, January 8, 2016 11:15 AM
  • First, i used simple body of the item to extract the information. Second, it is working fine with outlook 2010 but not with 2013. What's changed in these two versions?
    Tuesday, January 12, 2016 9:50 AM
  • Hi Lokanadhan,

    What’s the detail content of a sample mail?

    Could you reproduce that issue in other machine with office 2013?

    Regards

    Starain


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thursday, January 14, 2016 6:24 AM
    Moderator
  • Yes, i've seen this in few other machines with 2013 office.

    The body of sample email:

    • Project Lead: aa1111
    • Application Contact: a2s1s2
    • Request ID: 1111111
    • Project: 123534d-Project-124
    • VM Name: server@domain.com
    • VM IP Address: 1.1.1.1
    • Data Center:
    • vSphere DataCenter Path:
    • OS Selected: rhel6
    • Environment: Test

    I'm trying to get the VM Name here, which is working absolutely fine with 2010 office.

    Any ideas?

    And is the method i'm using good enough or do we have any other preferred method of extracting data?

    Thanks,

    Karthik

    Sunday, January 17, 2016 4:16 AM
  • Hi Karthik,

    Based on my test, it just the whitespace (HtmlBody,  ), so, you could remove whitespace by using Trim method or Replace(fqdn(1),”      “,””).

    Regards

    Starain


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, January 18, 2016 5:06 AM
    Moderator
  • Hi,

    Please try this workaround with regex

    Sub test_regexVM()
        Dim objRegex As Object
        Dim objRegM As Object
        Dim MailBody As String
        Dim i, GetVM
        MailBody = ActiveInspector.CurrentItem.Body
    
        Set objRegex = CreateObject("vbscript.regexp")
        With objRegex
            .Global = True
            .IgnoreCase = True
            .Pattern = "\nVM Name:.([a-z0-9][a-z0-9-.]{0,32}[a-z0-9]\@[a-z0-9][a-z0-9-]{0,32}[a-z0-9](?:\.[a-z]{2,5}){1,2})\b"
            If .test(MailBody) Then
                Set objRegM = .Execute(MailBody)
                For i = 0 To objRegM(0).SubMatches.count - 1
                    If InStr(1, objRegM(0).SubMatches(i), "@", vbTextCompare) Then
                        GetVM = objRegM(0).SubMatches(i)
                        Exit For
                    End If
                Next i
            Else
                GetVM = "No match"
            End If
        End With
        
        MsgBox GetVM
    
    End Sub


    Have a nice day. Oliv-

    Monday, January 18, 2016 4:41 PM
  • Thank you Starain.

    I was not able to replace them directly in the variable.

    But i used xlreplace function for the whole Column at the end.


    Thanks again.

    Machines don’t make Mistakes | we do.



    Tuesday, January 19, 2016 6:29 AM
  • I'll test this soon and let you know.

    Thanks.

    Machines don’t make Mistakes | we do.

    Tuesday, January 19, 2016 6:29 AM
  • Hi Karthik,

    >> I was not able to replace them directly in the variable. But i used xlreplace function for the whole Column at the end

    What do you mean you aren’t able to replace them in the variable but use xlreplace function at the end? What’s the different?

    On the other hand, please post the result here after you try the way that Oliv provided.

    Regards

    Starain


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Wednesday, January 20, 2016 3:17 AM
    Moderator
  • Apologies for delayed response.

    I was trying to replace the extra "* " directly by following command

    replace (fqdn(1), "~* ", vbnullstring) but it's not working.

    Then i tried columns("A:A").replace  what:="~*", replacement:=""

    It worked.



    Machines don’t make Mistakes | we do. ---Karthik

    Tuesday, January 26, 2016 4:09 AM
  • I have tried that code and it keeps on saying "No Match"

    Any Thoughts?


    Machines don’t make Mistakes | we do.

    Tuesday, January 26, 2016 5:17 AM
  • Hi Lokanadhan,

    Based on your reply, you want to replace “*”, this issue/question isn’t related to the original issue. Do you solve the original issue?

    Regards

    Starain


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Tuesday, January 26, 2016 7:34 AM
    Moderator