Email Solution

Hello Experts,

I am trying to find the best way to send emails to our clients and attach an inspection report that been generated as a PDF.

I have a CSV file that contains our client details and I want to set something up that does an email merge with the attachment. It is something I want to be able to schedule if possible.

Is there any programs that are reliable?

The only way I have thought of is the sending emails from excel using VBA. Then scheduling an script to run an macro in the spreadsheet to send the emails.

This process works but you need to have outlook open on the server and sometimes the spreadsheet gets locked and then hangs.

I am open to suggestion...

Thanks
Nick CollinsAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Wayne88Commented:
If you use VBS then you wouldn't need Outlook.  I have done email reports using VBS then schedule it in the task scheduler to automate it.  Also, ensure you have access to your email server to be able to open relay from a specific machine.  If not, then I see why you're relying on VBA in Excel in the first place.

Many examples online and here is one: https://www.experts-exchange.com/questions/24425236/VBScript-to-Attach-a-File-to-an-Email.html
0
Bill PrewIT / Software Engineering ConsultantCommented:
Do you still need help with this one Nick?  If so say a bit more about where you are, the environment this will work in, how the attachment part works (names change each execution, same for all recipients, etc).  DO you have access to an outgoing SMTP mail server that you can use so you don't need to use Outlook to send the emails?  Etc...


»bp
0
Nick CollinsAuthor Commented:
Yes please...

I have a folder that contains all the attachments and an CSV file that contains all the relevant data for the email.

In the CSV file it contains the name of the attachment file.

I have an access to a mail server and I would like to include an email signature.
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Bill PrewIT / Software Engineering ConsultantCommented:
Well, if you mean a "fancy signature" then that may be tricky without Outlook.  But if it's something that could be worked up in HTML, and added to the HTML body of outgoing email then that could work.


»bp
0
Nick CollinsAuthor Commented:
The signature could be made into HTML or I could add it as a JPEG image
0
Bill PrewIT / Software Engineering ConsultantCommented:
Okay HTML would be my first choice, embedding images can be a little tricky how they render at the other end.

Can you provide a sample of the CSV file?  What columns does it have, does it have a header line first, etc...


»bp
0
Nick CollinsAuthor Commented:
Here is an example output text file..

1TestData.txt

Just another issue that might occur is that the email address may be blank, if it is blank I want to be add an default email
0
Nick CollinsAuthor Commented:
The attachment name in the sample file  is "HV_000001_972956".

All the file are stored in a folder as a PDF document -

C:\Data\Attachments\HV_000001_972956.pdf

The folder path will not change, so if you could hard code the attachment path in the script and add the filename take from the text file
0
Bill PrewIT / Software Engineering ConsultantCommented:
Okay, I note that there are double quotes around the columns / fields.  VBS can easily parse at the delimiters, in this case commas, but it doesn't have any easy way to smartly handle the quoted values should they include a comma inside them.  Is it safe to assume that none of the data fields / columns will ever include an embedded comma in them?


»bp
0
Nick CollinsAuthor Commented:
There should be no commas in the fields
0
Bill PrewIT / Software Engineering ConsultantCommented:
I checked some of my prior solutions and I think I actually have a routine that handles them, so I'll leverage that to play it safe.


»bp
0
Nick CollinsAuthor Commented:
That's good...
0
Bill PrewIT / Software Engineering ConsultantCommented:
Okay, what is the body of the email going to look like?  Or do you want a shell from me and you work that out on your own?

Also, what type of authentication does the outgoing SMTP server require?  Can it do anonymous, or does it require an email and password to send?


»bp
0
Nick CollinsAuthor Commented:
Ok I need to get hold of the body of the email tomorrow and I need to find out the details of the email server. If I remember I think you need an email and address.
0
Bill PrewIT / Software Engineering ConsultantCommented:
Okay, I've got most of the shell built, pass along those details tomorrow and we'll go from there.  We might want to have a template file for the body HTML externally and then do some replacements of placeholders with inserted information.  Depends on how complex it is, and if it ever might change, etc.


»bp
0
Nick CollinsAuthor Commented:
That's fine.. is it possible to add a log file for the delivery of the emails..
0
Bill PrewIT / Software Engineering ConsultantCommented:
Yes, we can add logging to a file, can't really log the "delivery" but we can log the sending.  Would need more details on what you want to log, etc...


»bp
0
Nick CollinsAuthor Commented:
Just the log the email being sent..

Date and Time
Who it is going too?
File name of the attachment

That's it really
0
Nick CollinsAuthor Commented:
Template-Email-Body.txt

Email Body Text

{Record} = Insert Data from Text File
0
Nick CollinsAuthor Commented:
SMTP-Setup.txt

Some basic information what is required.
0
Bill PrewIT / Software Engineering ConsultantCommented:
Okay, a couple of follow up questions on the email template info:

  1. Where does the {Name} info come from, I assume this is the name of the recipient of each individual email, but I didn't see that in the list file, just their email address?
  2. Will (Company) and (Company Name) be in the email body template as constant text?  Or would you want them hard coded into the VBS script and dubbed into the email template like the {Record} data?
  3. Please clarify "This will be pointing at the attachment folder with the order number as the filename". Does this mean you don't want to actually attach the file to the email, just include a hyperlink to a file path?  Please expand.

Subject: Certificate - Order Number: {Record}

Dear {Name},

Title - Certificate

Client / Site / Job Number: {Record}
Order Number: {Record]
Completed On: {Record}
Completed By: {Record}
Certificate received on behalf of : {Record}


Your Certificate is attached for the work that has been completed on {Record} by (Company) at the above named site.

*** This is an automatically generated email, please do not reply ***


Regards

On behalf of (Company Name)

Attachment Filename:{Record} - This will be pointing at the attachment folder with the order number as the filename

Open in new window

And follow up on the outgoing email info:

  1. What is Name: used for from your file?  Or is that the Username for authentication to the email server?
  2. Is Email Address the from email address to use on the outgoing emails?

SMTP Set Up

Name:
E-Mail Address:
Mail Server Address:
SMTP Port:

UserName & Password required for authentication for outgoing messages

Open in new window


»bp
0
Nick CollinsAuthor Commented:
Question 1 - {Name} is the record under the heading 'Certificate received on behalf of :'

Question 2 - Company name to be hard coded in the VBS Script

Question 3 - Attachment

All the reports currently are stored in a folder in a format of 'YYMMDD' and the individual PDF reports are named after the order number in that particular folder. With my current email process I have hard coded the path of the attachment folder and placed the order number. So that the email picks up the correct PDF report as an attachment.

Email

The email, name & username all relate to the email account that is being used - I only provide that information as I thought it might be necessary
0
Bill PrewIT / Software Engineering ConsultantCommented:
Okay, this feels like a decent starting point.  Read it over, make sure you understand it and it matches what you thought we were doing.  I added enough comments that it should be clear the approach I was taking.  Also attaching the email template I used, this you can always edit to meet your needs further.

In the VBS file, make adjustments where you see "*** MAKE CHANGES HERE ***" near the top to specify your file and folder paths, email server information, and company info.

I used your earlier sample for the recipient list file so not including that here, all I did was change the email address for testing here and added some bad records for testing.

Image below is the email I received in testing here...

EE29096722.vbs

body.html

sshot-495.png

»bp
0
Nick CollinsAuthor Commented:
Thanks.. I have done the relevant setup and I am getting emails.

Just a few a little issues..

1.  Attachment Path - "D:\iauditor_exports_folder\Audits\20180509\"

Is there a way it could insert the 'YYYYMMDD' as part of the attachment path?

2. If there is no email address in the CSV file, could I add default email address.. so that the email gets sent to someone to take action##
0
Bill PrewIT / Software Engineering ConsultantCommented:
That's great you are getting emails out, getting the right settings for the SMTP server can sometimes be a challenge.

(1) So what you are looking for is if we have:

Const cAttachFolder = "D:\iauditor_exports_folder\Audits"

Then add on todays date in YYYYMMDD format to get the location to look for the attachments, so:

 "D:\iauditor_exports_folder\Audits\20180509"
If so then yes, that's easy enough.

(2) Yes as long as the email column in the CSV is there but blank then we can add another constant near the top for a default recipient.

Should anything about the sent email change (subject, etc) or by the mere fact that it goes to that specific default address will that be enough for someone to know what to do with it?


»bp
0
Nick CollinsAuthor Commented:
That's yes to all questions..

(2) it just gets emailed to the a specific default email address
0
Bill PrewIT / Software Engineering ConsultantCommented:
Okay, this should address those 2 items.

EE29096722.vbs


»bp
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Nick CollinsAuthor Commented:
All working now...

I need to try add and work out how I can put my email signature into the html body.

It involves several logos and address details
0
Nick CollinsAuthor Commented:
Just an add-on to the process...

I would like to send an summary email based on the entries in the CSV file.

Listing the names of the Client / Site / Job Number

Then adding an count against each one above and a grand total too
0
Bill PrewIT / Software Engineering ConsultantCommented:
Just an add-on to the process...
I would like to send an summary email based on the entries in the CSV file.
Listing the names of the Client / Site / Job Number
Then adding an count against each one above and a grand total too

You should have all the components in the code I provided to allow you to add that.


»bp
0
Nick CollinsAuthor Commented:
Should be able to work something out...

My colleague wants to insert the company logo into the html email template. Is that a simple process or tricky?
0
Bill PrewIT / Software Engineering ConsultantCommented:
If the logo image file is located on the internet and can be accessed with a URL, then it's pretty easy, basically just like including an image on a web page.

If it will only be on the local machine sending the email then it gets just a bit trickier, as the file has to be included in the email as a special attachment and some special syntax / methods have to be used to reference it in the HtmlBody content.


»bp
0
Nick CollinsAuthor Commented:
The image is stored on the server that is sending the emails. If it's too much dont worry about it but thank you for your help
0
Bill PrewIT / Software Engineering ConsultantCommented:
Let me look, I may have an example from prior question that you could try and understand.


»bp
0
Bill PrewIT / Software Engineering ConsultantCommented:
Take a look at my solution to this recent question, it has an embedded image in the HtmlBody that comes from local disk.

VBscript: send notification based on csv file and link html file

Pay particular attention to this code in the VBS:

        With .AddRelatedBodyPart("B:\EE\EE29094941\myimage.jpg", "myimage.jpg", cdoRefTypeLocation)
            .Fields.Item("urn:schemas:mailheader:Content-ID") = "<myimage.jpg>"
            .Fields.Update
        End With

Open in new window

And then this in the HTML:

<img src="cid:myimage.jpg" style=width:900px;height:125px;>

Open in new window


»bp
0
Nick CollinsAuthor Commented:
Is there any where in particular to put the code
0
Bill PrewIT / Software Engineering ConsultantCommented:
If you look at the prior solution I mentioned, it should give you some context there.  The VBS is related to the mail building logic in this solution, and the HTML would be in your logo code in the HTML template.


»bp
0
Nick CollinsAuthor Commented:
Well I have taken a look at the solution and I see the logic how it works but not being a programmer I can't get the script to work.
0
Bill PrewIT / Software Engineering ConsultantCommented:
Okay, don't have a ton of time, but maybe this will help you a little further.  If you need more help feel free to reach out to me in a private message, I do some consulting on the side...

Here are a new set of files, and below it the email that was generated, including an embedded image for the signature.  Hope this helps...
sshot-501.pngEE29096722.vbs
body.html
logo.gif
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VB Script

From novice to tech pro — start learning today.