Sunday, October 17, 2021

#1 2021-10-10 06:05:23 am

jennifer0124
Member
Registered: 2021-02-16
Posts: 1

Help with Reading Outlook messages and logging in Excel

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

Applescript:

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"

Applescript:

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


Filed under: applescript, excel, Outlook

Offline

 

Board footer

Powered by FluxBB

RSS (new topics) RSS (active topics)