Adding carriage return

[Wheelie686]

The following is partially made from code that I did not create so I can't claim to fully understand it. What it needs to do is take A1:B21 and email it out. However above the info from A1:B21, I would like to add data from another sheet (called Outages).

I can get it to do what I want except that instead of taking Outages!A2, A3, A4, etc and adding a carriage return, it runs it all together like one big paragraph. I am sure it's as simple as adding the right bit of code but I can't seem to put it in the right spot.

The current macro is

Please Login or Register  to view this content.

Any assistance would be appreciated

[djbomaha]

Hi Wheelie,

The command "vbNewLine" should add a carriage return to the body of your text. The line in your code that reads:

Please Login or Register  to view this content.

is an example of the "vbNewLine" command being used in a message box to provide a return in the message.

[Norie]

There doesn't seem to be anything in that code that does anything with 'Outages'.

[djbomaha]

...to add to my earlier post, I did notice that the commands "vbCrLf" and "vbNewLine" don't work with the ".HTMLBody" variable, they work with the ".Body" variable. If you want a good example, Ron DeBruin has one here where he adds carriage returns to an e-mail message.

http://www.rondebruin.nl/win/s1/outlook/bmail4.htm

[TMS]

Please Login or Register  to view this content.

Regards, TMS

[TMS]

I note that you have been on the board several times since a solution was provided.

I am interested to know if it resolved your problem.

Regards, TMS

[Wheelie686]

TMShucks - Your code provided me with the solution but now the info from A1:B21 is missing

[TMS]

Probably just misunderstood the requirement. Always best to provide a sample workbook.

I removed the select and then qualified the range. Now it has two ranges.

Please Login or Register  to view this content.

Regards, TMS

[Norie]

Wheelie

Which range do you want from Outages?

[Wheelie686]

Norie
Worksheet name is Outages
Range is A2 to indefinite. I never know how many rows of data could be there. It could be 0 or it could be 100 or more

[Norie]

Wheelie

But is it only column A from Outages?

[Wheelie686]

TMShucks - Sorry if the info I gave didn't give enough info to go on. In the future I'll give a bit of the workbook if I can.
With a bit of finetunning of where I want things placed, your new code did the trick. The only problem I foree is that the following line will only allow me to go down to B21. I could have alot of info then 21 rows. It could be 0 or it could be 100 or more.

Please Login or Register  to view this content.

[Wheelie686]

Yes Norie Only 1 column but it could be multiple rows down

[john55]

try this

Please Login or Register  to view this content.

[Wheelie686]

john55 - rng and rngO repeated themselves so I just used the following

Please Login or Register  to view this content.

The problem now lies with rng only giving me 19 rows of data even if I have more on my Outages sheet.

[Norie]

Wheelie

Do you want columns A and B from Outages, or just column A?

Columns A & B.

Please Login or Register  to view this content.

Column A.

Please Login or Register  to view this content.

[john55]

I just tested and it gives me the correct number of rws, see wkb attached

[TMS]

As far as I am aware, the OP has not provided sufficient information for a definitive solution to be given.

I started with the assumption that the data required was in the Outages worksheet ... a fixed range. Then, when an issue was identified, I repeated the original range and duplicated it on the Outages sheet.

If the number of rows on either of the sheets varies, it should only be a matter of establishing the last row on each of the sheets.

I think it is down to the OP to provide the data so that we don't spend time stabbing in the dark.


Regards, TMS

[Wheelie686]

TMShucks
Part 1 of my email is from my Outages worksheet which is not a fixed range. The entire sheet could be blank or it could be A2 to A infinity. The # of rows would never decrease but could potentially increase.
Part 2 of my email is a fixed range of A1:B21 on a sheet called NBReport Outage Client E-Mail

[Wheelie686]

john55 - I'm at a loss to explain why yours works and mine doesn't. The only difference should be my sheet is called Outages and yours is called Sheet1...

[john55]

if you make a copy of yr file without sensitive data and upload it here perhaps someone can see the issue...

[Wheelie686]

Thanks everyone for your help, I've been away for a few weeks unexpectedly. The code I have now is as follows and the only issue I am having now is instead of taking everything from A1 down on the Outages page, it's taking only the last populated row.

Please Login or Register  to view this content.

Does anyone have any insight? The specific line that I think is causing the issue is as follows

Please Login or Register  to view this content.

[TMS]

Please Login or Register  to view this content.

Regards, TMS

[TMS]

Please Login or Register  to view this content.

Regards, TMS

[TMS]

Thanks for the rep.



If you are satisfied with the solution(s) provided, please mark your thread as Solved.


New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save


You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.