Tyler Tyler -4 years ago 250
AppleScript Question

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

Answer Source

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

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download