Macro that auto generates Email within Excel

**Gambinus** · 05-26-2010, 02:16 PM

I use a Macro that when run, will generate an email with the excel sheet attached. See attached example.

My two questions are:
- is it possible for the body of the email that is generated to have spaces within the text? So it shows more like the below:
"Good morning,

Please find today's forecast for Friday, May 07, 2010 attached.

Thank you,

FRC Treasury Team"

- Also, the colors in the spreadsheet change automatically when the macro attaches the sheet. Why is this? Is it possible to do something so that this does not happen?

Thank you,

**6StringJazzer** · 05-26-2010, 02:46 PM

For the spacing, try this:

    Msg = "Good morning,  " & Chr(10) & _
       "Please find today's forecast for Friday, May 07, 2010 attached. " & Chr(10) & _
       "Thank you," & Chr(10) & _
       "FRC Treasury Team"

I'm not sure about the colors, but I'm guessing that the original file was created in Excel using a custom mapping for color indices, and when the new file is created is uses the default color indices. But I have not seen this before so I'm not sure.

**DP978** · 05-26-2010, 02:53 PM

I am almost positive there is an HTMLbody method that allows you to insert carriage returns and spaces via html.

Edit: Try -

.BodyFormat = olFormatHTML
.HTMLBody = "<HTML><BODY>Enter the message text here. </BODY></HTML>"

use <br/> to enter a new line and <p>, </p> for paragraphs.

**JBeaucaire** · 05-26-2010, 03:08 PM

This will give you the format of the message body you requested:

    Msg = Chr(10) & "Good morning," & Chr(10) & Chr(10) & _
          "Please find today's forecast for Friday, May 07, 2010 attached." & Chr(10) & Chr(10) & _
          "Thank you," & Chr(10) & Chr(10) & _
          "FRC Treasury Team"

If your attached file is changing somehow, you could convert that file to flat values, removing all the formulas and leaving it locked with the current values. Like so:

       'Create a workbook from the ActiveSheet and save it as a temporary file.
        WkbShts = Excel.Application.SheetsInNewWorkbook
          Excel.Application.SheetsInNewWorkbook = 1
          ActiveSheet.Copy
          ActiveSheet.Cells.Copy
          ActiveSheet.Range("A1").PasteSpecial xlPasteValues
          TempFile = TempPath & ActiveSheet.Name & ".xls"
          ActiveWorkbook.SaveAs TempFile
          ActiveWorkbook.Close
        Excel.Application.SheetsInNewWorkbook = WkbShts

**ckolawall** · 01-26-2013, 03:21 PM

Hey what if in a message I need it customized.
For instance I want it to have part of the excel cells in the message.

So if Im sending it to bob.... I can input in the macro bob.....

**Pepe Le Mokko** · 01-26-2013, 03:24 PM

Welcome to the Forum, unfortunately:

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.