I am trying to export mails from Outlook on a Mac using AppleScript as it does not work with VBA. However, I don't progress a lot, the text files are not saved to my Desktop and that's why it always stops with an empty Excel sheet. Any hints?
-- Step 1: Export emails with the Red category to text files set desktopFolderPath to (path to desktop) as text
tell application "Microsoft Outlook" set redCategoryName to "Red"
set redEmails to {}
set allEmails to every message of inbox
repeat with anEmail in allEmails
if (subject of anEmail) contains redCategoryName then
set end of redEmails to anEmail
end if
end repeat
end tell
-- Export emails with the Red category to text files tell application "Microsoft Outlook" repeat with redEmail in redEmails set emailSubject to subject of redEmail set receivedTime to receivedTime of redEmail set filePath to desktopFolderPath & (my formatReceivedTime(receivedTime) & "_" & emailSubject & ".txt")
set mailBody to content of redEmail
set fileRef to open for access file (POSIX file filePath) with write permission
write mailBody to fileRef
close access fileRef
end repeat
display alert "Emails with the Red category have been exported to text files."
end tell
-- Step 2: Extract filtered information from text files and write to an Excel file tell application "Microsoft Excel" set excelFilePath to desktopFolderPath -- Replace with your desired Excel file path
set newWorkbook to make new workbook
tell newWorkbook
set activeSheet to active sheet
set rowIndex to 2 -- Start from row 2 for data
set cellValue of range "A1" of activeSheet to "Sender"
set cellValue of range "B1" of activeSheet to "Email Reference"
set cellValue of range "C1" of activeSheet to "Names in the Mail"
set cellValue of range "D1" of activeSheet to "Email Addresses in the Mail"
set cellValue of range "E1" of activeSheet to "Phone Numbers in the Mail"
set cellValue of range "F1" of activeSheet to "Date Received"
-- Get the list of text files in the folder
set textFolderPath to (POSIX file desktopFolderPath) as alias -- Replace with your folder path
set textFiles to every file of textFolderPath whose name extension is "txt"
-- Loop through each text file and extract filtered information
repeat with textFile in textFiles
set filePath to (POSIX path of (textFile as alias))
set fileContent to read textFile
set senderName to ""
set emailReference to ""
set names to ""
set emailAddresses to ""
set phoneNumbers to ""
set dateReceived to ""
-- Extract the desired information from the fileContent
-- Modify this section to extract the information based on your requirements
-- Write the filtered information to the Excel worksheet
set cellValue of range ("A" & rowIndex) of activeSheet to senderName
set cellValue of range ("B" & rowIndex) of activeSheet to emailReference
set cellValue of range ("C" & rowIndex) of activeSheet to names
set cellValue of range ("D" & rowIndex) of activeSheet to emailAddresses
set cellValue of range ("E" & rowIndex) of activeSheet to phoneNumbers
set cellValue of range ("F" & rowIndex) of activeSheet to dateReceived
set rowIndex to rowIndex + 1 -- Move to the next row for data
end repeat
save workbook as excelFilePath
close newWorkbook saving no
end tell
display alert "Filtered information has been written to the Excel file."
end tell
-- Helper function to format received time on formatReceivedTime(receivedTime) set AppleScript's text item delimiters to {" ", "/", ":"} set receivedTimeItems to text items of (receivedTime as string) set formattedTime to (item 1 of receivedTimeItems & "-" & item 2 of receivedTimeItems & "-" & item 3 of receivedTimeItems & "_" & item 4 of receivedTimeItems & item 5 of receivedTimeItems & item 6 of receivedTimeItems) set AppleScript's text item delimiters to "" return formattedTime end formatReceivedTime
