CODEWITHSUNDEEP
excelscriptingoutlookapplescript
Tyler
Tyler

Reputation: 2063

Applescript: Can't copy Date and Message Content from Outlook to Excel

I am trying to copy outlook e-mails from a folder and copy it over to an excel document. I currently have it pulling the Subject and the Sender but I am having two main issues. One, I can't pull the date with the date property. Two, I cannot pull only the message content as it pulling all of the HTML from the outlook message. Here is my full code right now...

    tell application "Microsoft Excel"
    set LinkRemoval to make new workbook
    set theSheet to active sheet of LinkRemoval
    set formula of range "D1" of theSheet to "Message"
    set formula of range "C1" of theSheet to "Subject"
    set formula of range "B1" of theSheet to "From"
    set formula of range "A1" of theSheet to "Date"
end tell

tell application "Microsoft Outlook"
    activate
    set theRow to 2
    set theMessages to messages of mail folder "Requests"
    repeat with aMessage in theMessages
        my SetFrom(sender of aMessage, theRow, theSheet)
        my SetDate(date of aMessage, theRow, theSheet)
        my SetSubject(subject of aMessage, theRow, theSheet)
        my SetMessage(content of aMessage, theRow, theSheet)
        set theRow to theRow + 1
    end repeat
end tell

on SetDate(theDate, theRow, theSheet)
    tell application "Microsoft Excel"
        set theRange to "A" & theRow
        set formula of range theRange of theSheet to theDate
    end tell
end SetDate

on SetFrom(theSender, theRow, theSheet)
    tell application "Microsoft Excel"
        set theRange to "B" & theRow
        set formula of range theRange of theSheet to name of theSender
    end tell
end SetFrom

on SetSubject(theSubject, theRow, theSheet)
    tell application "Microsoft Excel"
        set theRange to "C" & theRow
        set formula of range theRange of theSheet to theSubject
    end tell
end SetSubject

on SetMessage(theMessage, theRow, theSheet)
    tell application "Microsoft Excel"
        set theRange to "D" & theRow
        set formula of range theRange of theSheet to theMessage
    end tell
end SetMessage

Also, when the code pastes the message content over to the excel file, it isn't pasting all of the text, it only pastes 250 chars... see image below.

excel file showing issue of message content not displaying

Upvotes: 0

Views: 243

Answers (1)

ShooTerKo
ShooTerKo

Reputation: 2282

I tried something and found out the following:

Use the values time received and plain text contentinstead of dateand content:

tell application "Microsoft Outlook"
    activate
    set theRow to 2
    set theMessages to messages of mail folder "Requests"
    repeat with aMessage in theMessages
        my SetFrom(sender of aMessage, theRow, theSheet)
        my SetDate(time received of aMessage, theRow, theSheet)
        my SetSubject(subject of aMessage, theRow, theSheet)
        my SetMessage(plain text content of aMessage, theRow, theSheet)
        set theRow to theRow + 1
    end repeat
end tell

Concerning the other problem I had success using set value of cell to ... instead of set formula of range to ...:

on SetMessage(theMessage, theRow, theSheet)
    tell application "Microsoft Excel"
        set theRange to "D" & theRow
        set value of cell theRange of theSheet to theMessage
    end tell
end SetMessage

The visible cell's content is less than the whole mail content, but you can scroll through it and will find the whole (plain text) content.

Have fun, Michael / Hamburg / Germany

Upvotes: 1

Related Questions