Sending attachments to a list of Email addresses in CSV

September 20, 2016 at 19:25:16
Specs: Windows 7
Hi,

I have a directory - which is updated on a daily basis with CSV files. I then will have another CSV file (can be in another directory) which will hold three columns:
CustomerCode, EmailAddress, AttachmentName
e.g.: C100000, test@test.com, COMPANYNAME_C100000

I need to create a bat file to run on a scheduled task every day to look into this directory - then send out the CSV files to the right customers.

I'm thinking using Blat would be a good fit.

The thing is - that there won't be an attachment for every customer every day - so if there is no attachment with the customer number in it - then no email should be sent

This seems like it is fairly close to what I need - but I can't seem to it to work:
http://www.computing.net/answers/pr...

Any help would be hugely appreciated.

Regards
Rick


See More: Sending attachments to a list of Email addresses in CSV

Reply ↓  Report •


#1
September 20, 2016 at 20:15:43
I've wasted a few hundred hours on command line mailers.
Let's assume blat can do this.

::====== script starts here ===============
::
:: RickBLAT.bat 2016-09-21 10:06:19.53
@echo off & setLocal enableDELAYedeXpansioN

:main
for /f "tokens=1-3 delims=, " %%a in (myfile.csv) do (
if "%%c" neq "" echo Blat.exe -to %%b -attach %%c
)
goto :eof
::====== script ends here =================

=====================

M2 Get custom script or take private lessons


Reply ↓  Report •

#2
September 20, 2016 at 20:30:18
Hi Mechanix2Go,

Fair enough!

So would I literally enter the below into a filename rickBLAT.bat?

::
:: RickBLAT.bat 2016-09-21 10:06:19.53
@echo off & setLocal enableDELAYedeXpansioN
:main
for /f "tokens=1-3 delims=, " %%a in (myfile.csv) do (
if "%%c" neq "" echo Blat.exe -to %%b -attach %%c
)
goto :eof


When I try to run this - the cmd box flashes on the screen for a split second - then disappears. There haven't been any emails sent. Just so you know - my Blat.exe is in my System32 folder - do I need to reference the full folder address?

Regards
Rick


Reply ↓  Report •

#3
September 20, 2016 at 21:06:16
Here's a prototype to start from (hell, we never get it right the first time anyway!):
@echo off & setlocal
:: collection of attachments - what are their names? assuming company_cust#
set atts=c:\outgoing
:: file containing targets and attachments if any
set mainlist=c:\customers\list.csv
for /f "tokens=1-3 delims=," %%a in (%mainlist%) do if "%%c" neq "" call :blat %%b %%c
goto :eof

:blat
echo sending:
echo Blat.exe c:\xxx\Messagebody.txt -to %1 -attach %atts%\%2
::---- end script

I made many assumptions, all of which will be wrong.


Reply ↓  Report •

Related Solutions

#4
September 20, 2016 at 21:34:34
Hi nbrane,

Thanks for the message.

This is what I have now:

@echo off & setlocal
:: collection of attachments - what are their names? assuming company_cust#
set atts=C:\Users\rick\Desktop\blat3216\full\tosend
:: file containing targets and attachments if any
set mainlist=C:\Users\rick\Desktop\blat3216\full\myfile.csv
for /f "tokens=1-3 delims=," %%a in (%mainlist%) do if "%%c" neq "" call :blat %%b %%c
goto :eof
:blat
echo sending:
echo Blat.exe C:\Users\rick\Desktop\blat3216\full\Messagebody.txt -to %1 -attach %atts%\%2

This doesn't do anything.

I have tested blat - and it's working from my computer.
Does it matter that Blat.exe is in the SYSTEM32 folder?

Can you see anything wrong with the above?

Just so you know - here is a sample of what is in myfile.csv

CustomerCode EmailAddress Attachment
C102676 test@test.com TEST_C102676.csv
C102678 test2@test.com TEST_C102678.csv

In Messagebody.txt all i have in there is:
this is a test message body

Within the directory: C:\Users\rick\Desktop\blat3216\full\tosend is the following files:
TEST_C102676.csv
TEST_C102678.csv

Thanks
Rick


Reply ↓  Report •

#5
September 20, 2016 at 23:13:30
"When I try to run this - the cmd box flashes"

Means you're not running it at the prompt.

=====================

M2 Get custom script or take private lessons


Reply ↓  Report •

#6
September 20, 2016 at 23:25:23
Hi,

Yes that's right - as I need it be within a bat file so that I can setup a scheduled task.


Reply ↓  Report •

#7
September 20, 2016 at 23:37:23
I've tried running the script from the prompt - and it indicates %%a was unexpected at this time.

Reply ↓  Report •

#8
September 21, 2016 at 17:11:19
My bad. The script I posted does not try to execute blat, it only shows the blat commandline to see if it looks correct. I usually don't run a "hot" test until I can see what is going to happen - especially with destructive or "propogative" (is that a word?) code. Email is a very critical operation, so I did not want to cause something that would send email out that had bad content.
With that understanding, best thing for testing is to ouput to an intermediate text or batchfile, which can be examined without wreaking havoc. Change this line:
echo Blat.exe c:\xxx\Messagebody.txt -to %1 -attach %atts%\%2
to:
>>c:\users\rick\desktop\testblat echo Blat.exe c:\xxx\Messagebody.txt -to %1 -attach %atts%\%2

this will load your desktop with a new file "testblat" which you can view in notepad. It will most likely not be correct, which is why we don't want to run it "live" nor to schedule it in schtasks. If or when you come back with followup, we can proceed (with caution, ha ha!) I hope I'm not trying your patience, but email mistakes can be very embarrassing and not rescindable.
ps: If blat.exe is in your system32, it should not present a path problem for a batch.
pps: M2 was also using "safe mode" in his code, so nothing was or will be executed until you "arm" the script for live ammo. we don't want that, at this stage.

message edited by nbrane


Reply ↓  Report •

#9
September 21, 2016 at 17:31:27
Hi,
Thanks for the reply.
I understand the need to test - which is why in my list of email addresses I've only got two email addresses - each one is mine. So if something doesn't look quite right - then no harm done.

I've done the changes per the above - but when I try to run the bat file it flashes the prompt for a split second and I can manage to see Sending: Access is Denied.

Initially I thought this was due to the script referencing Blat.exe when the executable was in the SYSTEM32 folder - so I put tried the absolute exe address - but this resulted in the same issue.

For reference - this is the script I am trying to run at the moment:

@echo off & setlocal
:: collection of attachments - what are their names? assuming company_cust#
set atts=C:\Users\rick\Desktop\blat3216\full\tosend
:: file containing targets and attachments if any
set mainlist=C:\Users\rick\Desktop\blat3216\full\myfile.csv
for /f "tokens=1-3 delims=," %%a in (%mainlist%) do if "%%c" neq "" call :blat %%b %%c
goto :eof
:blat
echo sending:
>>C:\Users\rick\Desktop\blat3216\full echo Blat.exe C:\Users\rick\Desktop\blat3216\full\Messagebody.txt -to %1 -attach %atts%\%2

Regards
Rick


Reply ↓  Report •

#10
September 21, 2016 at 17:52:05
echo sending:
>>C:\Users\rick\Desktop\blat3216\full

is trying to write textfile to a directory directly(ha ha). You need a filename:
echo sending:
>>C:\Users\rick\Desktop\blat3216\full\test.txt ...
pause

I added a pause to keep the "black screen flashing off" from happening, so you could see...


Reply ↓  Report •

#11
September 21, 2016 at 18:01:14
haha - yep fair enough.

i've made the changes - and it worked - this is the resulting text file contents:

Blat.exe C:\Users\rick\Desktop\blat3216\full\Messagebody.txt -to rick@rick.com -attach C:\Users\rick\Desktop\blat3216\full\tosend\COMPANYNAME_C102676.csv
Blat.exe C:\Users\rick\Desktop\blat3216\full\Messagebody.txt -to rick@gmail.com -attach C:\Users\rick\Desktop\blat3216\full\tosend\COMPANYNAME_C102678.csv


Reply ↓  Report •

#12
September 21, 2016 at 18:25:15
Well, since you're in safe mode, try renaming the file extension to ".bat" and executing it. Should work, far as it goes at this point.
If it worked, then remove the "echo" and perhaps make a logfile Here's a recap with echo removed, and some kind of logfile:
@echo off & setlocal
:: collection of attachments - what are their names? assuming company_cust#
set atts=C:\Users\rick\Desktop\blat3216\full\tosend
:: file containing targets and attachments if any
set mainlist=C:\Users\rick\Desktop\blat3216\full\myfile.csv
for /f "tokens=1-3 delims=," %%a in (%mainlist%) do if "%%c" neq "" call :blat %%b %%c
goto :eof
:blat
Blat.exe C:\Users\rick\Desktop\blat3216\full\Messagebody.txt -to %1 -attach %atts%\%2
>>C:\Users\rick\Desktop\blat3216\full\daily.log echo %date% %time% Blat.exe C:\Users\rick\Desktop\blat3216\full\Messagebody.txt -to %1 -attach %atts%\%2

message edited by nbrane


Reply ↓  Report •

#13
September 21, 2016 at 18:39:11
It executes - but no emails come through?
Could there be an issue with the port that blat is installed with?

Reply ↓  Report •

#14
September 21, 2016 at 19:11:22
Have you tried a simpler test? like (from command prompt!):
Blat.exe C:\Users\rick\Desktop\blat3216\full\Messagebody.txt -to rick@rick.com

I tried a "bare bones" blat transmission and got error:
Blat v3.0.7 (build : Jul 20 2012 01:24:19)
32-bit Windows, Full, Unicode

Sending blat.hlp to ___@suddenlink.net
Subject: Contents of file: blat.hlp
Login name is ____
*** Error *** The SMTP server does not like the sender name.
*** Error *** Have you set your mail address correctly?

So I can't really do much until I get my own "house in order" on this end. I think it used to work, but never really used it much. Does your blat throw any errors? Port should not be a problem, since it's well-established for smtp.


Reply ↓  Report •

#15
September 22, 2016 at 20:47:17
Hi nbrane,

The simpler test works for when sending email internally.
However when I try to email an external address it comes back with error about not being able to relay.

This is fair enough as on our corporate network - the server is setup to not be an open relay. I have done some research and I'm looking to implement a workaround for this.

Until then - I'm going to have to sit on this.

Did you "get your house in order" so that you can test?

Cheers
Rick


Reply ↓  Report •

#16
September 22, 2016 at 21:36:19
No, sorry to say. I worked on it, but blat, which used to work, no longer does. Funny how forgers can get right in to my email account and spoof me all over, and I can't even log in at base level (blat or netcat). B64 should have worked, but didn't for "auth login".

Reply ↓  Report •

#17
September 22, 2016 at 23:32:05
Hi, OK - so I've sorted out my issue.
I can now perform the simple task of using blat to send to internal as well as external email addresses.

However when I attempt to run the bat file with the more complex task - it doesn't relay any emails.

Any further thoughts on this?


Reply ↓  Report •

#18
September 24, 2016 at 10:49:20
1) If you could post the code you're trying to run it would help I assume it's based on my post #12, but it would help to see exactly what you've got..
2) I believe that schtasks requires that all paths be explicit. Make sure any path references in your code are fully qualified - both data-files and executables.

Reply ↓  Report •

#19
September 28, 2016 at 22:43:49
hi,

1) here is the code as I have it at the moment:
@echo off & setlocal
:: collection of attachments - what are their names? assuming company_cust#
set atts=C:\Users\rick\Desktop\blat3216\full\tosend
:: file containing targets and attachments if any
set mainlist=C:\Users\rick\Desktop\blat3216\full\myfile.csv
for /f "tokens=1-3 delims=," %%a in (%mainlist%) do if "%%c" neq "" call :blat %%b %%c
goto :eof
:blat
echo sending:
echo blat C:\Users\rick\Desktop\blat3216\full\Messagebody.txt -to %1 -attach %atts%\%2
pause

2) i am not using schtasks yet - I am simply running the .bat file


Reply ↓  Report •

#20
September 29, 2016 at 21:11:43
OH! The "echo" is still preceding third from last line. You can leave it in for info, but copy that line like this:
echo blat C:\Users\rick\Desktop\blat3216\full\Messagebody.txt -to %1 -attach %atts%\%2
rem here's the actual execution of blat - substitute full path for "c:\blat\"
rem to point to wherever you have blat. copy line WITHOUT echo:
c:\blat\blat.exe C:\Users\rick\Desktop\blat3216\full\Messagebody.txt -to %1 -attach %atts%\%2

Reply ↓  Report •


Ask Question