to line up email body of an automated email process

Hi Experts,

I have VBA to send email, in the email body I want to add some data like as:

Date                quantity    value         part number    
01/01/2013          1           20.23        12345
01/02/2013         100        2012.23   43432
01/02/2013         100         232012.23   43432  <----------
How to make it a clean lineup not like above.

when I have a longer data in value it just cant make clean line just change like raw three above.
please advise if any VBA code that fixed the column size and do not move like that.

Thanks
alam747Asked:
Who is Participating?
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.

Boyd (HiTechCoach) Trimmell, Microsoft Access MVPDesigner and DeveloperCommented:
If you use the HTML format for the email body you can use HTML Table tags to create and format  a nice looking table.
0
alam747Author Commented:
Data pulling from an excel sheet not all data in a raw therefore if there any way to make it happend the thing I am looking for.

Thanks
0
Jeffrey CoachmanMIS LiasonCommented:
Remember, you must consider what will happen if one of the values in the "Value" column exceeds the space you have allotted for it...

So in that sense, I am in agreement with TheHiTechCoach, (use the .HTMLBody keyword and use UTML to format the data.)
Note that this can be somewhat confusing to do for two reasons:
1. If you have never worked with HTML before
2. The syntax can become confusing because of the combination of HTML and VB and the different ways they display strings

If you want to keep this simple, then why not just create a PFD of the Excel sheet and attach that as a file to the email?

JeffCoachman
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

Boyd (HiTechCoach) Trimmell, Microsoft Access MVPDesigner and DeveloperCommented:
I agree with JeffCoachman that using a PDF attachment keeps it simple.

Data pulling from an excel sheet not all data in a raw therefore if there any way to make it happend the thing I am looking for.

You can read data from an Excel worksheet or Access tables and /or queries to populate the email message body.

Are you doing this with Access VBA code?
0
Jeffrey CoachmanMIS LiasonCommented:
THTC,

I'll will leave this in your capable hands now...

I can only do HTML as far as <br>, <b>, <u>, <i>

;-)

Jeff
0
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPDesigner and DeveloperCommented:
Jeff, thanks for the assist!
0
alam747Author Commented:
I finally decided to convert the excel sheet to htm and use as email body

MyMail.HTMLBody = RangetoHTML

the problem is the workbook is open to fill data from other excel files in a loop while it find then copy to report.xlsx then want to convert html column in each loop with data.

Set wb_Source_01 = xl.Workbooks.Open("C:\data.xlsx") <-- contain data to collect in a loop until end of the sheet for specific customer therefore report.xlsx will be overwritten once converted to html and send the email.

Set wb_Source_03 = xl.Workbooks.Open("C:\report.xlsx")

some customer has one lines of data some has five lines not fixed.


    SourceFile = "C:\Test\report.xlsx"    <-- any way to use wb_Source_03 here
    SourceSheet = "datatomail"    
    SourceRange = "$A$1:$F12"  <-------  how to put the linecounter here instead of fixed value
    HTMLFile = "C:\Test\Test.htm"
     
    wb_Source_03.Close True  <--- it works if I close here if there any way to avoid it becase want to close at end of entire data from for each
        With xl
       .Visible = False
       Set xl = .Workbooks.Open(SourceFile, , False)  <---- give error if I do not close it because its already open
   End With

    With xl.PublishObjects.Add(xlSourceRange, HTMLFile, "datatomail", SourceRange, xlHtmlStatic)
        .Publish (True)
    End With
    'Load the HTML text into a string
    Set ts = fso.GetFile(HTMLFile).OpenAsTextStream(1, -2)
    RangetoHTML = Replace(ts.ReadAll, "align=center x:publishsource=", "align=left x:publishsource=")

the way to change the SourceRange to veriable and how to convert worksheet to html without closing the workbook during the loop.

Thanks
0
alam747Author Commented:
Hi Experts,
I am waiting for your advise how to get rid of those problem marked as <--- to use the html email body in an automated email process.

I appreciate your prompt response.

Thanks
0
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPDesigner and DeveloperCommented:
I think you are trying to do Excel Automation. Is that correct?  is so, you may want to add the Excel Zone to your question to get some experts in Excel to jump in.

I do a lot of Excel automation but it is exporting to Excel. I create Report sin Excel from Access data. I rarely have the need to read the data from Excel files. I have yet to have to read data from linked workbooks.

It is possible to send the email from Excel.  It may work better in your case to do it all In Excel.  See: http://www.rondebruin.nl/win/section1.htm
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
alam747Author Commented:
Hi Experts,

Finally, I could able to convert excel to htm format which gave almost as I am looking for.
Only problem is when it published to htm from from excel sheet the line width changed like as below;

In excel it look like:
 This is a test for excel to htm file
 This is a test for excel to htm file
This is a test for excel to htm file


while published to html it looks like:
This is a test for excel to htm file This
is a test for excel to htm file
This is a test for excel to htm file

I format the cell to horizontally distributed but once it published to html it changed.

Used the following code in my access module:

    SourceFile = "C:\test\test_Cover_letter.xlsx"   ' for debug
    SourceSheet = "Cover"    
    SourceRange = "$A$2:$A31"
    HTMLFile = "C:\test\Cover.htm"
   
    With xlo
        .Visible = False
        Set xlo = .Workbooks.Open(SourceFile, , False)
    End With
   
    With xlo.PublishObjects.Add(xlSourceRange, HTMLFile, "Cover", SourceRange, xlHtmlStatic)
    .Publish (True)
    End With
    'Load the HTML text into a string
    Set ts = fso.GetFile(HTMLFile).OpenAsTextStream(1, -2)
    RangetoHTML = Replace(ts.ReadAll, "align=center x:publishsource=", "align=left x:publishsource=")
    ts.Close

later used as email body like as below:

MyMail.HTMLBody = RangetoHTML

it works but with the above problem.

Would you please advise to get the same format ( line width ) as it shown in excel sheet where need to be modified ?

Thanks in advance for your prompt response.

Thanks
0
alam747Author Commented:
thanks
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
Microsoft Applications

From novice to tech pro — start learning today.