Automatic correcting in Outlook

[joemajestee]

Hello,

I have a macro in Excel 2003 that creates emails in Outlook 2003 using a .oft file. The emails end up with blank lines because not all of the records contain data for each line. Right now, the macro displays the emails so the user can delete all the blank lines before sending. I'm wondering if there is a way to use VBA to automate some of that.

Next is a sample of the emails, the bullets that are circles as opposed to dots are supposed to be indented.

•	The entry number must appear in the PDF file name. 
•	
•	One email with exhibit materials should be submitted per individual entry. 
•	No other exhibits are required for this entry., 
•	
•	, 
o	
o	
o	
o	
o	
o	
o	
•	Be sure to review the judging criteria for your entry prior to submitting your exhibit. 
•	
•

I looked at the idea of using REPLACE to eliminate the listitems but I could not find a way to do that because the html wasn't consistent.

Thanks

Joe

 

[SkipVought]

hi,

How does the program get the data?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[joemajestee]

Hi Skip.
Sorry, juggling lots of things today.

Not sure what you mean. But here's some of the excel code:

        Dim OutApp As Object, OutMail As Object
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItemFromTemplate(strTemplate)
strHTMLbody = OutMail.htmlbody
... replace stuff in strHTMLbody with some of the current row's content
OutMail.htmlbody = strHTMLbody
OutMail.Subject = Replace(OutMail.Subject, Replace(Cells(1, c1.Column).Value, " ", ""), strReplacement)

Does this answer your question?

 

[SkipVought]

Let's try this...

The emails end up with blank lines because not all of the records contain data for each line.[/quote
Where do "all of the records" come from, that you refer to?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[joemajestee]

OK, thanks for the clarification.
The excel spreadsheet has in row one what I'm gonna call headers.
header1, header2, header2...header50

Each row has unique data
record1, record2, etc.

The outlook template file has text (formatted html) like this:

Hello header1,
Your entry number is header2 and it's name is header3.
Here is a list of stuff you have to do before the judges come out:
[ul]
[li]header4[/li]
[li]header5[/li]
[li]header6[/li]
[li]header7[/li]
[li]header8[/li]
[/ul]
Thanks for entering, header1.

The macro goes through row by row and creates a separate email for each row, substituting the headern text it finds with the current row, column n's value. Most of the rows won't have data in all of the list item columns. If the current row has nothing in the same column as header6, then the macro replaces "header6" with "".

Hope that's clearer.

 

[SkipVought]

Well then, the program ought to check each row (record) for the presence of data, before passing it to the message.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[joemajestee]

I'm not creating the html on the fly. All of the placeholders (headers) that might contain data are in the template. I can check for data, but I'd still have the problem of "headern" still being in the resulting email.

 

[SkipVought]

Header/row: who cares what YOU call it. Before placing the data in your message, your program must TEST the Header/row data for the presence of data!

If this is NOT in your program, then why are you posting in a VBA (program) forum?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[joemajestee]

Because the only answer, if there is one, will be VBA.

The emails are created using VBA and an existing outlook template. The template has placeholders for all of the possible data from each record. If the record doesn't require all of the placeholders, the placeholders remain in the template and have to be manually deleted. It would be better if they could be programmatically deleted.

I used the word header to distinguish it from data because that is how the macro works, sort of like a mail merge.

sample worksheet
FirstName---LastName---Entry#---Task1---Task2---Task3
Herman--------Munster--------36B--------Clean-----Paint-----------------
Gomez---------Addams--------32B---------Call--------------------Cook

sample template:
FirstName LastName:
Your entry number is Entry#.
Do these things:
[ul]
[li]Task1[/li]
[li]Task2[/li]
[li]Task3[/li]
[/ul]
Thanks for your support, FirstName

Results:
-------------------------------------------------
Herman Munster:
Your entry number is 36B.
Do these things
[ul]
[li]Clean[/li]
[li]Paint[/li]
[li][/li]
[/ul]
Thanks for your support, Herman
-------------------------------------------------
Gomez Addams:
Your entry number is 32B.
Do these things
[ul]
[li]Call[/li]
[li][/li]
[li]Cook[/li]
[/ul]
Thanks for your support, Gomez
-------------------------------------------------

What am I not telling you?

 

[SkipVought]

...which is the inherent problem with source data that is not normalized!

You would be better served with source data like...
[tt]
FirstName LastName Entry# Task
Herman Munster 36B Clean
Herman Munster 36B Paint
Gomez Addams 32B Call
Gomez Addams 32B Cook
[/tt]


Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[dhulbert]

Skip.

Not sure if normalised data would help here,

Person 1 has 5 items of data
person 2 has 3 itmes of data
Person 3 has 2 items of data

If he has 5 place holders in the e-mail template and there is only data for 3 then the other 2 will remain empty and will display a blank line.

What I think the guy is really looking for is a way to delete empty place holders AFTER the e-mail has been generated but before it is sent to the recipient.



I love deadlines. I like the whooshing sound they make as they fly by
Douglas Adams
(1952-2001)

 

[joemajestee]

yes, that is correct, thank you. Skip?

 

[SkipVought]

The problem is with your template.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]