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

i have little experience of vba or macros will, no doubt, become self-evident. have got point can create email if in worksheet holds information. step out of worksheet start issues. have tried changing activeworkbook.envelopevisible = true line refer pertinent worksheet failing. have tinkered while , tried few permutations , struggling understand need do.

help (please)!

ideally have workbook show worksheets users need see; means hiding & protecting worksheets have formula identify correct email address(es), subject (various concatenated componants) , range of cells (formatted , spaced set out in worksheet) body (as body html or rtf rather attachment). on buton click email created , sent (with user told has happened).

we have office 2010 outlook in use.

i have adapted script http://support.microsoft.com/kb/816644 below:

sub marefemail() ' marefemail macro ' create email provide referral service      ' select range of cells on active worksheet.    worksheets("referrals").range("p93:ab113").select     ' show envelope on activeworkbook.    activeworkbook.envelopevisible = true     ' set optional introduction field thats adds    ' header text email body. sets    ' , subject lines. message    ' sent.    worksheets("referrals").mailenvelope       .introduction = "referral requested 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  msgbox "referral email sent yyy service"  end sub 

all welcome.

background

i have been building income/expediture calculator benefit's assessment, bill estimators , number fo flags , notifications built in housing association aimed support low-income families. has calculations identify (if pertinent) loss of benefits income due recent welfare reform - both local , national benefits - , savings. organisation has properties across on 100 local authorities each own rules.

it used front line staff (some technophobic) applicant ususally in front of them.

the workbook identifies people meet criteria flag offer of referral services might (e.g. employment & training support). there 6 different services, each of has number of providers , restrictions, location-based. referral works flagging applicant has met criteria (to benefit service) , suggests applicant offered service. if applicant says "yes" concatenated hyperlink clicked prefills to, cc & subject fields depending on location, service, household make-up , property type. subject includes timeframe referral (as give customer).

each subject bespoke. bespoke subject used people recieving referral identify priorities while in inbox without having review body of email; bespoke subject set allow sorting per referral agency's requirements (i.e. each 1 different!).

the member of staff needs move worksheet copy , paste range of cells body of email. each of 6 referral types has different range of cells , send email. process ensures quality of information required each referral.

speed key driver improvements "tool". @ point of trying find waste steps , speed improvements. automating email save 2 minutes per interview , reduce chance of errors.

you need hide worksheeets understand it.

the worksheets part of collection in workbook.

you can either iterate through them index:

dim iindx integer  iindx=1 worksheets.count     if worksheets(iindx).name<>"referrals"         worksheets(iindx).visible=xlsheethidden     else         worksheets(iindx).visible=xlsheetvisible     end if next 

or member

dim osheet worksheet  each osheet in worksheets     if osheet.name<>"referrals"         osheet.visible=.visible=xlsheethidden     else         osheet.visible=.visible=xlsheetvisible     end if next 

so can hide other sheets prior making envelope visible, , unhide them later.

hth philip