Hello folks,
Thank you in advance for your help. What I am trying to do is have Applescript open my excel spreadsheet go the next open row and write the data from the emails in the zProcessing subfolder. I was able to get it to work if I just set the row to row 2 but I am getting errors when I try to use the ‘last row’.
I am also having trouble adding in the recipient email address to cell E for each email if you want to try to tackle that part too.
This is the working version with row 2 defined
tell application "Microsoft Excel"
set LinkRemoval to "/Users/Jennifer/Desktop/ShipmentTrackingSheet.xlsm"
set theSheet to item "Order Confirmation Export"
open LinkRemoval
activate sheet "Order Confirmation Export"
set theSheet to active sheet
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"
set lastFilledCell to get end (range "A65536") direction toward the top
set firstBlankCell to get offset lastFilledCell row offset 1
set therow to firstBlankCell
log firstBlankCell
select firstBlankCell
end tell
tell application "Microsoft Outlook"
activate
set theAccount to exchange account "IMS Supply"
set therow to 2
set topFolder to mail folder "Inbox" of theAccount
set subFolder to mail folder "zProcessing" of topFolder
set subFolder2 to mail folder "S7 Order Confirmations" of topFolder
set theMessages to messages of subFolder
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
move aMessage to subFolder2
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 cell theRange of theSheet to theMessage
end tell
end SetMessage
save workbook
This is my cobbled together try on getting it to select the last row. I am open to writing it in an open cell, like L1, and referencing that if I need to.
It is finding the value based on the notes "set formula of cell “L1” to range “‘[ShipmentTrackingSheet.xlsm]Order Confirmation Export’!$A$251” but nothing is written to that cell.
The error that I am getting in this one is “The variable therow is not defined”
tell application "Microsoft Excel"
set LinkRemoval to "/Users/Jennifer/Desktop/ShipmentTrackingSheet.xlsm"
set theSheet to item "Order Confirmation Export"
open LinkRemoval
activate sheet "Order Confirmation Export"
set theSheet to active sheet
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"
set lastFilledCell to get end (range "A65536") direction toward the top
set firstBlankCell to get offset lastFilledCell row offset 1
set LinkRemoval to "/Users/Jennifer/Desktop/ShipmentTrackingSheet.xlsm"
set theSheet to item "Order Confirmation Export"
select range ("L1")
set formula of cell "L1" to firstBlankCell
end tell
tell application "Microsoft Outlook"
activate
set theAccount to exchange account "IMS Supply"
set therow to value of firstBlankCell
set topFolder to mail folder "Inbox" of theAccount
set subFolder to mail folder "zProcessing" of topFolder
set subFolder2 to mail folder "S7 Order Confirmations" of topFolder
set theMessages to messages of subFolder
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
move aMessage to subFolder2
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 cell theRange of theSheet to theMessage
end tell
end SetMessage
save workbook