Solved

How do I send a html Outlook 2010 email containing the active worksheet in Excel 2010

Posted on 2015-02-03
20
170 Views
Last Modified: 2016-02-11
Hello experts,

I would like to have a macro that will email the current active sheet to an outlook user and use HTML formatted text from a template in the workbook.

The To: filed should be populated from cell 'C5' of the active worksheet and the Subject: and Body: should come from the template worksheet.

I had a similar problem that an expert helped me with.  This script would email the active sheet, but it does not send in html or use a templat worksheet.  Maybe the code below will help

Unfortunatley my remit was updated and now I need to send HTML whereas I didnt previously.

Sub EmailRecertToC5WithBody()
' Works in Excel 97 through Excel 2007.
Dim FileExtStr As String
Dim FileFormatNum As Long
Dim Sourcewb As Workbook
Dim Destwb As Workbook
Dim TempFilePath As String
Dim TempFileName As String
   
With Application
   .ScreenUpdating = False
   .EnableEvents = False
End With

Set Sourcewb = ActiveWorkbook
' Using ActiveSheet.Copy creates a new workbook with
' the sheet and the file format is the same as the
' original workbook.
' Copy the worksheet to a new workbook.
ActiveSheet.Copy
Set Destwb = ActiveWorkbook

' Determine the Excel version and file extension/format.
With Destwb
   If Val(Application.Version) < 12 Then
      ' You are using Excel 97-2003.
      FileExtStr = ".xls": FileFormatNum = -4143
   Else
      ' You are using Excel 2007.
      ' When you use ActiveSheet.Copy to create a workbook,
      ' you are prompted with a security dialog. If you click No
      ' in the dialog, then the name of Sourcewb is the same
      ' as Destwb and you exit the subroutine. You only see this
      ' dialog when you attempt to copy a worksheet from an .xlsm file with macros disabled.
      If Sourcewb.Name = .Name Then
         With Application
            .ScreenUpdating = True
            .EnableEvents = True
         End With
         MsgBox "Your answer is No in the security dialog."
         Exit Sub
      Else
         Select Case Sourcewb.FileFormat
            ' Code 51 represents the enumeration for a macro-free
            ' Excel 2007 Workbook (.xlsx).
            Case 51: FileExtStr = ".xlsx": FileFormatNum = 51
            ' Code 52 represents the enumeration for a
            ' macro-enabled Excel 2007 Workbook (.xlsm).
            Case 52:
               If .HasVBProject Then
                  FileExtStr = ".xlsm": FileFormatNum = 52
               Else
                  FileExtStr = ".xlsx": FileFormatNum = 51
               End If
            ' Code 56 represents the enumeration for a
            ' a legacy Excel 97-2003 Workbook (.xls).
            Case 56: FileExtStr = ".xls": FileFormatNum = 56
            ' Code 50 represents the enumeration for a
            ' binary Excel 2007 Workbook (.xlsb).
             Case Else: FileExtStr = ".xlsb": FileFormatNum = 50
         End Select
      End If
   End If
End With

' Change all cells in the worksheet to values, if desired.
''   With Destwb.Sheets(1).UsedRange
''      .Cells.Copy
''      .Cells.PasteSpecial xlPasteValues
''      .Cells(1).Select
''   End With
''Application.CutCopyMode = False

'Save the new workbook and then mail it.
   TempFilePath = Environ$("temp") & "\"
   TempFileName = ActiveSheet.Name

With Destwb
   .SaveAs TempFilePath & TempFileName, FileFormat:=FileFormatNum
      On Error Resume Next
    Dim OutApp As Object
    Dim OutMail As Object
    Dim strbody As String

    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)


    With OutMail
        .To = Destwb.Sheets(1).Cells(5, 3)
        .CC = ""
        .BCC = ""
        .Subject = "with body"
        .Body = "Whatever body you want" & Chr(13) & Chr(13) & "Whatever body you want"
        .Attachments.Add (Destwb.Path & "\" & Destwb.Name)
        .Display 'or use .Display or .Send
    End With
    On Error GoTo 0

    Set OutMail = Nothing
    Set OutApp = Nothing

      On Error GoTo 0
   .Close SaveChanges:=False
End With
 
' Delete the file you just sent.
Kill TempFilePath & TempFileName & FileExtStr

With Application
   .ScreenUpdating = True
   .EnableEvents = True
End With
End Sub

Open in new window

ExcelEmail1.xlsx
0
Comment
Question by:wisemat
  • 11
  • 9
20 Comments
 
LVL 29

Expert Comment

by:gowflow
ID: 40586319
So basically you want to email each and every sheet in the workbook as a separate attachment of that sheet with the text that exist in sheet email template ?

If my assumption is correct then in the body you have a mix of English and Spanish I guess is that right ? and then between each line you have a blank line is that what you want in the final email ?

Then the To is cell c5 of the worksheet that is fine provided cell c5 has an email address not a name !!! (check that)
The subject you say cell B3 Subject goes here What is that ???? B3 of the template or what ??

Let me know
gowflow
0
 

Author Comment

by:wisemat
ID: 40586398
Hey Gowflow,

So this is a continueation of what you helped me with yesterday (attached) - you helped me take raw data and split into individual sheets, now I need to mail those sheets.

In response to your questions:
Yes email each worksheet as a separate email to the person named on on the individual worksheet in cell C5

The body is just dumy text it will be a paragraphed piece of text, if could go into one cell if this is easier, but is harder for me to edit.

I will ensure that cell c5 contains either an Outlook DisplayName or a .com email address (display is prefered)
The subject will be determined by the template only so will be same for all eamils

Ulimately this will be a two button setup, 1) Compile data into separate sheets 2) press to email active/current sheet

Hope that helps
ExcelExample-V02.xlsm
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40586518
Not really where do we get our body from ??? and the subject in what cell ? I kow the TO is in cell C5 that is clear what about the rest ?
gowflow
0
 

Author Comment

by:wisemat
ID: 40586565
The subject and the body will not change per email/worksheet,  itwould be nice if they were in a separate worksheet as a template. I thought to make these efforts re-usable it would be good to have an email template

There doesnt 'have' to be a template it can be hard coded into the VBA

I would like some basic formatting on the text, paragraphs,colour/bold etc  The text has not been finalised yet so I created some dummy text in the example?

more useful?  sorry it's hard to aritculate
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40586621
You are asking for a bicycle but I will give you a Maserrati !!!! how about that ? (with all modesty)

You create in outlook the email (template) that you want when you want ... with the formatting that you want and the colors and underline and bullets all you can use (for sure in HTML) and once done (a general one) you save it to Draft and

The macro when you run it you will simply need to have prior running it Open Outlook and just select that email in Draft and the Macro will use that email as body and Subject and will Attach to it the worksheet one by one and will start the Email with Dear 'value of C5'

line feed
line feed
<Your body that you created>

and will save this new created email in Draft for you to check and simply send them manually,

Once all is fine and fine tuned then will replace 1 instruction so they are sent automatically.

Agree with this ???
gowflow
0
 

Author Comment

by:wisemat
ID: 40586656
WOW!
0
 

Author Comment

by:wisemat
ID: 40586761
Heading home, but so exciteed I will log on when i get there to see the magic!
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40586833
So hurry up and create your template in outlook to test the macro !!! :)

Leave 2 blank lines then start your text in the emails the salutation will come from the macro !

gowflow
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40586869
Here it is !!!

In sheet Template there is a button Generate Emails (don't worry it will only save in draft and display them for you) activate the button and check it. It will create emails for all sheets in the workbook except any sheet that have the word template and any sheets called MasterData.

Let  me know.
gowflow
ExcelEmail1-V01.xlsm
0
 

Author Comment

by:wisemat
ID: 40587028
Awesome, testing. BRB
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 29

Expert Comment

by:gowflow
ID: 40587082
ok posted on duty !!!

don't forget to add pictures if you want in your draft email .... anything ...

:)
gowflow
0
 

Author Comment

by:wisemat
ID: 40587095
Looking good!

I get the drafts created (the emails stay open too, could be a problem whren i start sending large amounts).

I assume changing .Display to .Send will send the email rathe than drafts them, right?
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40587099
exactly and if you want to scrutinize them just comment out .Display at first it will save them in draft you go and see them when all is ok you just delete them from draft you do this as many times as you want and once your set you put .send and run the macro and here you are

BINGO !

gowflow
0
 

Author Comment

by:wisemat
ID: 40587157
Drafting works 100%
Sending sadly does not, It send the first one, then i thinkit has problems deleting the data an dmoving tpo the next one.
I get an error which i have attached as a png


All i did was swap .Display for .Send
error.png
0
 
LVL 29

Accepted Solution

by:
gowflow earned 500 total points
ID: 40587174
remove the .save

and try again.

BTW are you sure that all hv the email address in C5 ?
gowlfow
0
 

Author Comment

by:wisemat
ID: 40587217
That's got it!

I put a bad email in for one test and it gave an error which is good, I can then see what needs to be corrected!

This is a first class solution! Thank you so much, would love to buy you a beer or three or tip you but I don't think thats allowed.
You truely are the master of VBA

I can't say it enough
Thank you!
0
 

Author Closing Comment

by:wisemat
ID: 40587241
World class support!
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40587264
WOW your making me blush !!!
no problem my guest and my pleasure anytime.

Note though we offer paid support if your interested you click on my profile and send me a msg.

or else we are available in here ! pls feel free to post s link in here for any other issue you may need help with,
by posting a copy of the address of the new question in here.
gowlfow
0
 

Author Comment

by:wisemat
ID: 40594158
Hi gowflow

I have a follow-on issues. I am able to send emails using the script however my colleagues get s runtime-error 287

Any thoughts
0
 

Author Comment

by:wisemat
ID: 40594161
I do have local admin rights which I don't think they have. Is there a work around as I'm going on holiday?
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Sometimes Outlook might have problems sending a message. There may be various causes- corrupted PST, AV scanner etc. The message, instead of going to the Sent Items folder, sits in the Outbox indefinitely. To remove it you can use a free tool cal…
Not sure what the best email signature size is? Are you worried about email signature image size? Follow this best practice guide.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now