SMTP Mail with changing or variable to addresses

[jimbobmccoy]

hi

i have a similar request to the one in this thread;

http://www.mjtnet.com/forum/smtpsendmai ... t6241.html

essentially i have an excel file that i need to send to list of recipients based on data in the file.

i can export the data (email addresses) from the excel file to a txt file that will look like this;
[email protected]
[email protected]
[email protected]
and i understand the sending by SMTP part, but even after reading the thread above, im unsure as to how to get the send to run through and populate the to addresses from the txt file.

in an ideal world id have this compiled in to an exe too.

im not adverse to figuring this out myself if someone can give me some pointers, or a script with an explanation so i know how for future.

TIA

[Marcus Tettmar]

So I think you are asking how to loop through a text file and send an email for each line in the file?

Look at the ReadFile command and see the example in the help file. Let's assume your file has one email address per line and nothing else. We could loop through it like this:

Code: Select all

//read the file into the filedata variable
ReadFile>inputfile.txt,filedata
//split the data up by lines into an array of lines
Separate>filedata,CRLF,lines
If>lines_count>0
  //assuming there are any, loop through them one by one
  Let>k=0
  Repeat>k
    Let>k=k+1
    Let>this_address=lines_%k%

    //we now have the address for this line in this_address, we could send it an email
    SMTPSendMail>this_address,bla,bla,.......

  Until>k=lines_count
Endif

Fill in the rest. This will send one email per address.

You mentioned your data is in Excel. If that's the case you don't need to be extracting to a text file first, you could have Macro Scheduler read the data direct from Excel and loop through the excel file. For an example of that look in your Samples group in Macro Scheduler and you'll see an example macro called "Extract From Excel". It loops through an Excel sheet row by row and sends the data to Notepad. Instead of sending data to Notepad replace with an SMTPSendMail command - assuming one of the columns is an email address ...

[jimbobmccoy]

so thinking on this a little further, i am now wondering if i can set up a loop that checks the txt file and if a value exists runs the smtp send, and if it doesnt it moves to check the next value?

As i'll only have 40 odd values to check, i could set up the 40 smtp sends to each address in the loop, and then it will only send if the result is positive. It may not be the neatest but should work?

[jimbobmccoy]

[quote="Marcus Tettmar"]So I think you are asking how to loop through a text file and send an email for each line in the file?

Look at the ReadFile command and see the example in the help file. Let's assume your file has one email address per line and nothing else. We could loop through it like this:

[code]//read the file into the filedata variable
ReadFile>inputfile.txt,filedata
//split the data up by lines into an array of lines
Separate>filedata,CRLF,lines
If>lines_count>0
//assuming there are any, loop through them one by one
Let>k=0
Repeat>k
Let>k=k+1
Let>this_address=lines_%k%

//we now have the address for this line in this_address, we could send it an email
SMTPSendMail>this_address,bla,bla,.......

Until>k=lines_count
Endif[/code]

Fill in the rest. This will send one email per address.

You mentioned your data is in Excel. If that's the case you don't need to be extracting to a text file first, you could have Macro Scheduler read the data direct from Excel and loop through the excel file. For an example of that look in your Samples group in Macro Scheduler and you'll see an example macro called "Extract From Excel". It loops through an Excel sheet row by row and sends the data to Notepad. Instead of sending data to Notepad replace with an SMTPSendMail command - assuming one of the columns is an email address ...[/quote]

sorry i must have been posting while you replied. i shall read through your post and hopefully emerge wiser

[Marcus Tettmar]

Of course. You can do anything you want.

If you mean "if a value exists" do you mean some lines in the file may be blank? If that's what you mean then just add this when pulling out the line:

Code: Select all

Let>this_address=lines_%k%
If>this_address<>{""}
   SMTPSendMail>this_address,bla,bla,.......
Endif

Of course you may mean something else. If you want, paste a sample of your text file here. If there are more items on each line - i.e. not just the email address - you can split the line up by a delimiter and use Separate to get each part, in the same way I used it above to split out each line from the file.

You can use string parsing, regex matching etc etc - Infinite possibilities - if you want to paste your file here and explain what you want to do I'm sure we can help better.

[jimbobmccoy]

thanks for the repsonses.

the original file is a report that will have data for a group of 6 recipients from a possible 40. the group members will change daily.

the file will have a column that has the recipients email address in and this is what is being pulled out to the text file (only done as i assumed it would need to be to get the smtp send to pick the right values out-the method you mention of pulling addresses direct from the file itself seems much better).

at present the file is being opened daily, the recipients noted, and then an email composed to them.
id like to automate this, so i thought originally either create one smtp send, and have the recipients be variables that are driven by the txt file;
SMTPSendMail>variableemail1;variableemail2,variableemail3etc192.168.2.2,[email protected], Reports, sheet,Please find todays sheet attached,Y:\IT\somefile.xls
but am now thinking to have 40 smtp sends to each recipient set up with the check before each one to determine if it should send or not.

i'll have a look through the second post of code you mention along with the help for the section you menttion and try to come up with a sample of what i think will acheive what i need, and then post that up if thats ok?

thanks