***Wrong sheet gets pasted in the outlook mail***

**[email protected]** · 06-22-2014, 10:42 AM

Hi Friends,

I have twice posted this query but still unsolved

The change I required is, I am sending mails for some of the email id by attaching particular sheet and pasting the sheets in the body of the mail (As per existing VBA script)

Here I am facing some problem, if the particular sheet is not in the work book, it is taking previously copies sheet and pasting in the body of the mail.

I have two sheet in the work book (Sheet1 & Sheet2) and I am sending mails to two mail id

1 mail: to [email protected] need to paste sheet 1 and sheet 2. Since both sheets are there in the work book it is pasting correctly

2nd Mail: to [email protected] need to paste sheet 9 and sheet2. Since Sheet 9 is not in the work book it is wrongly pasting sheet 1. My requirement is if the mentioned sheet is not in the work book VBA should not paste anything and should be blank. Hence mail to [email protected] should paste only sheet 2.

Kindly help to modify if the sheet is not exist in the work book, it should not copy previously copied sheet. I have attached the macro workbook for your reference.

Regards,
Prabhu

**Richard Buttrey** · 06-22-2014, 11:08 AM

Hi,

I'm not quite sure why you email macro needs to repeat some of the lines, and no doubt that could be tidied up. However perhaps the most logical way would be to Dim a variable that says whether Sheet9 is present and then set the variable accordingly, i.e.

Dim sht9 As Boolean
Dim ws As Worksheet

Please Login or Register  to view this content.

Then in the second part of your emailing macro after you've set rng in the With Sheets("Sheet9") block use the following instead

Please Login or Register  to view this content.

Incidentally it's much better practice to use VBA code names for sheets rather than sheet tab names. Some day you'll come unstuck when a user decides to change a sheet tab name.

**Norie** · 06-22-2014, 11:13 AM

The first thing you should do is remove the On Error stuff like this.

Please Login or Register  to view this content.

Then I would suggest you don't repeat the same code twice, including the RangeToHTML function.

**[email protected]** · 06-22-2014, 11:55 AM

Hi Richard,

I have very little knowledge in the VBA.

Could you plz incorporate the changes in the Excel. I have tries but unable to update.

Wrong sheet gets pasted in the outlook mail

LinkBack

Thread Tools

Rate This Thread

Display