Send an Outlook email from Excel putting formatted text from a hidden worksheet into the body of the email

Question

I have very little experience of VBA or macros as will, no doubt, become self-evident. I have got to the point that I can create the email perfectly if I am in the worksheet that holds all the information. As soon as I step out of that Worksheet then I start to get issues. I have tried changing the ActiveWorkbook.EnvelopeVisible = True line to refer to the pertinent worksheet but am failing. I have tinkered for a while and tried a few permutations and am struggling to understand what I need to do.

Help (please)!

Ideally I would like to have the workbook only show the worksheets that the users need to see; this means hiding & protecting the worksheets that have the formula to identify the correct email address(es), subject (various concatenated componants) and the range of cells (formatted and spaced as set out in the worksheet) for the body (as body html or rtf rather than an attachment). So on buton click an email is created and sent (with user told this has happened).

We have Office 2010 with Outlook in use.

I have adapted the script from http://support.microsoft.com/kb/816644 to the below:

Sub MArefEmail()
' MArefEmail Macro
' Create email to provide the referral for the service


   ' Select the range of cells on the active worksheet.
   Worksheets("Referrals").Range("P93:AB113").Select

   ' Show the envelope on the ActiveWorkbook.
   ActiveWorkbook.EnvelopeVisible = True

   ' Set the optional introduction field thats adds
   ' some header text to the email body. It also sets
   ' the To and Subject lines. Finally the message
   ' is sent.
   With Worksheets("Referrals").MailEnvelope
      .Introduction = "Referral requested by applicant during pre-tenancy interview."
      .Item.To = "service@xxx.org.uk"
      .Item.CC = "first.last@xxx.org.uk"
      .Item.Subject = Range("b43").Text
      '.Item.Send
   End With

MsgBox "Referral email sent to yyy service"

End Sub

All help welcome.

Background

I have been building an income/expediture calculator with a benefit's assessment, bill estimators and a number fo flags and notifications built in for a Housing Association aimed to help support low-income families. It also has calculations to identify (if pertinent) any loss of benefits income due to recent Welfare Reform - both local and national benefits - and savings. The organisation has properties across over 100 local authorities each with their own rules.

It is used by front line staff (some technophobic) with the applicant ususally in front of them.

The workbook also identifies people that meet certain criteria to flag the offer of a referral to services that might help (e.g. Employment & Training support). There are six different services, each of which has a number of providers and restrictions, some location-based. The referral currently works by flagging that the applicant has met the criteria (to be likely to benefit from the service) and suggests the applicant is offered the service. If the applicant says "Yes" then a concatenated hyperlink clicked that prefills the To, Cc & Subject fields depending on the location, service, household make-up and property type. The Subject also includes the timeframe for the referral (as give to the customer).

Each subject is bespoke. The bespoke subject is used by the people recieving the referral to identify priorities while in the inbox without having to review the body of the email; the bespoke subject is set up to allow sorting as per the referral agency's requirements (i.e. each one is different!).

The member of staff then needs to move to another worksheet to copy and paste a range of cells into the body of the email. Each of the six referral types has a different range of cells and then send the email. This process ensures the quality of information required for each referral.

Speed is a key driver for improvements to the "tool". I am at the point of trying to find waste steps and speed improvements. Automating the email could save up to 2 minutes per interview and reduce the chance of errors.

Answer 1

You need to hide certain worksheeets as I understand it.

the worksheets are part of a collection in the workbook.

You can either iterate through them by index:

dim iIndx as integer

For iindx=1 to worksheets.count
    if worksheets(iindx).name<>"Referrals" then
        worksheets(iindx).visible=xlSheetHidden
    else
        worksheets(iindx).visible=xlSheetVisible
    end if
next

or by member

dim oSheet as worksheet

for each osheet in worksheets
    if osheet.name<>"Referrals" then
        osheet.visible=.Visible=xlSheetHidden
    else
        osheet.visible=.Visible=xlSheetVisible
    end if
next

so you can just hide all the other sheets prior to making the Envelope visible, and unhide them later.

HTH Philip