Thanks for the reply, I have undertaken to to send mails based on the database of email ID and FullName in a xls file and have landed up with some issue and unable to resolve some issue
posting the code so that i may help others and if the experts have time, pls do comment
-- *hi I am just 1 month old on apple so excuse newbie issues*
-- Experts Pls correct if I have not used the best practise in AppleScripts
-- Script to SendMail using a dB in Xls file
-- Resources required
-- 1. MAC OS X
-- 2. Execl for MAC
-- 3. MAIL.app
-- Files Used Format --
-- 1. dB file Xls
-- Xls file format
-- Name of worksheet "List of Email ID"
-- Col A | Col B | Col C | Col D | Col F |
-- Classification of Customer | Email ID | Name | Company | Mailing Status |
-- Not used now | Used | Used | Not Used | Not Used |
-- 2. template draft message in DraftMail Box
-- Format
-- Start of Mail
-- Space
-- Space
-- Body of Msg
-- Space
-- Space
-- Signatures
-- Space
-- Attachment: (Have put this txt so that the attachments are after the signature)
-- Bug Dec 2009
-- 1. Does not work with out attachments, will fix this shortly.
-- 2. reply_subject cannot be used with a var in make new --- properties {subject:reply_subject, visible:false}
-- Crashed Mail.app when reply_subject is not a constant string --? many help is highly appriciated
-- Ack & Copyright
-- thanks to the posting on the multiple support site
-- this is a typical newbie coding copy paste from many sources and complied for
-- the requirments
-- for the script to work pls ammend the following --
-- Pls enter the following ---
-- The Subject of the Mails ---
set reply_subject to "Invitation to Event"
-- Your Name
set sender_name to "Srikanth Kamath"
-- Your email address.
-- DO Not Change any other Line Below ---
-- Your Account. This is the email address of your OUTGOING mail account.
set full_sender_email to sender_name & " " & sender_email
try
-- to trap any General error and display them --
try
-- trap any error within the open file loop
set sourceFolder to (path to desktop folder as Unicode text) & "SendMail:"
set TemplateFolder to sourceFolder as alias
set oldDelims to AppleScript's text item delimiters
set MsgTitle to "AppleScript to SendMail"
set ErrorBeep to 3
set theWorkbookFile to choose file with prompt "Please select an Excel workbook file:"
set theWorkbookName to name of (info for theWorkbookFile)
set flag to true
delay 1
beep 1
tell application "Microsoft Excel"
open theWorkbookFile
set theWorkbook to active workbook
end tell
on error errText number errNum
-- Just Experimenting with OnError equiv of VB --
if (errNum = -1409) then
display dialog "NO File found" with icon 0 with title MsgTitle
else
--if (errNum = -128) then
beep ErrorBeep
display dialog "Closing Script" & return & return & "Pls Open Excel File with EmailID & Name" with icon 2 with title MsgTitle buttons "STOP"
--Quit the Script But does not work?
set flag to false
quit
--end if
end if
end try
-- Not sure if Finder is Required set afile without finder works --
-- May some expert will coment on this --
if flag is true then
tell application "Finder"
activate
set aFile to (choose file with prompt "Choose a Invite Attachment: " default location TemplateFolder)
end tell
end if
-- Routine to Get the dB from Xls File --
tell application "Microsoft Excel"
activate
tell theWorkbook
tell worksheet "List of Email ID"
set rangeData to value of used range
set rowUsed to (count rangeData)
-- to do --
-- display dialog rowUsed buttons "OK" default button "OK"
-- May be provide a selection of row to send if the list is large to avoid
-- SPAM filter from trigering?
-- Col F Value can be changed to "Sent" and run the script again on Col F Blanks
-- or may be use script to schedule send and keep all the mails in draft
-- Some day i shall complete this .... ;-)
set theEmailList to {}
set theEmailListName to {}
set theMessages to value of range ("B2:C" & rowUsed) as list
repeat with i from 2 to rowUsed
set inquiry_email to ""
set inquiry_email to value of range ("B" & i & ":C" & i) --("B" & i) --("B" & i)
if inquiry_email is not in theEmailList then set theEmailList to theEmailList & {inquiry_email}
-- to do --
-- Someday Someday will develop on this ....
-- Just a list for develping this script with Company Filter ?
--set inquiry_sender to ""
--set inquiry_sender to value of range ("C" & i)
--if inquiry_sender is not in theEmailListName then set theEmailListName to theEmailListName & {inquiry_sender}
end repeat
end tell
end tell
close theWorkbook saving no
end tell
-- Routine to Create the Mail --
-- Uses a Msg in the DraftMail Box; this is the most simple way to create a template
-- Not sure if Mail has any template options;
tell application "Mail"
activate
-- Keep the original setting of the Mail.app
set DefaultEmailFormat to default message format
set statusQuoteMsg to quote original message
set default message format to rich text
-- ~set the features
-- found no documentation on how to set the preferences parameter of Mail.app
set quote original message to false
set fileContents to ""
-- You have to set up a template draft message first
-- to do IMPORTANT --
-- Normally i use Lowercase to do a str comparision but did not find any case
-- comversion routine that worked
display dialog "Enter The Subject of the Draft Mail" with title MsgTitle default answer "" buttons {"Cancel", "Continue"} default button 2
set the SearchStr to text returned of the result
-- find the Draft Message in DraftMail box
set ListofMsginDraft to every message of drafts mailbox
set EmailSubject to {}
repeat with thisone in ListofMsginDraft
set TempStr to thisone
-- set TempStr to lowerCase(TempStr)
-- set SearchStr to lowerCase(Searchstr)
if subject of TempStr is SearchStr then
set EmailSubject to thisone
set fileContents to content of thisone
exit repeat
end if
end repeat
-- continue if subject is matched in the draftmail box --
if EmailSubject is not {} then
--set replysubject to TempStr --EmailSubject??
repeat with theRecip in theEmailList
-- not very sure of these routines --
set the_text_item to list 1 of theRecip
set EmAdd to text item 1 of the_text_item
set EmName to text item 2 of the_text_item
--display dialog EmAdd buttons "OK"
--display dialog EmName buttons "OK"
-- create new mail message and set Subject Line
set ReplyMail to make new outgoing message at beginning of outgoing messages with properties {subject:reply_subject, visible:false}
--bug subject:reply_subject cannot be used with variable str
tell ReplyMail
make new to recipient at beginning of to recipients with properties {address:EmAdd, name:EmName}
set sender to full_sender_email
--set subject to replysubject does not work
set content to "Dear " & EmName & "," & return & return & fileContents
-- Some issue some mails donot have the paste and only the attachments
-- when using the visible:false
-- No problems when using visible:True
-- ? Any Comments welcomed
-- added the delay to circumvent this issue
beep 1
delay 2
make new attachment with properties {file name:aFile} at after the last paragraph
-- ** Signatures **
-- signature do not work yet : work a round use signature in the draft message
-- ** Any Help is Welcomed --
end tell
beep 1
-- if the following code is not inserted then the mails are in the Draft mail box
-- use the schedule to send mails from the DraftMail box
send ReplyMail
end repeat
--set AppleScript's text item delimiters to oldDelims
set default message format to DefaultEmailFormat
set quote original message to statusQuoteMsg
else
display dialog "Pls Enter the Subject Correctly" with title MsgTitle buttons "OK" default button "OK"
end if
end tell
on error errText number errNum
-- Just to trap any error Not yet developed
display dialog errText with icon 0 with title MsgTitle buttons "QUIT"
quit me
end try
--- end script