Formatting a paragraph from some excell cells into another program

[Redants]

Hello,

I have just started getting into VBA programming and I hope someone can point me in the right direction about a simple problem I have.
Basically I have created a command button that submits the active worksheet into a new outlook email, populating the recipient, subject and the main body of the email.
This works great, but I cannot find information on how to format the main body of text. What I am trying to do is create a list in the main body of text that looks up the information in 10 cells (within 1 column). I can create this as a string of text. I have tried to place each tag (I believe that is the right term) on a seperate line, but I cannot do this.

My question is this, what is the code to format the populated information as either a list or how to effectivly press enter after each tag?

Any and all advice would be greatly apprciated! If there is an article on basic commands for formatting automatically populated text in outlook (or word, if the coding is the same) then that would be fantastic! I have searched the site, but alas I have found nothing.

Many thanks

[Ago]

so your problem is only to make the [enters]? or did i read it wrong?

the string you have, is that an array? or is it just an string with all the 10 cellvalues in?

to answer the question about the enters, Chr(10) will do the enter.
example: MsgBox("Line 1" & Chr(10) & "Line 2")
this will create a messagebox with two lines saying Line 1 on the first line and Line 2 on the second line. the Chr(10) will most likely work in your email aswell.

its quite hard to help you when you dont give us anything to work with.
im guessing its not always the same 10 cells so when you put them in your string maybe you should put them in a array instead to make it easier?



For i = 1 to 10
'paste your code to find the cell you want
array(i) = ActiveCell.Value
next i


since i dont know how to paste text in to a mail i cant help you with the second part but it should be something like



mailbody = array(1)
For i=2 To 10
mailbody =& Chr(10) & array(i)
Next i

[Ago]

i just noticed im an idiot.
so there is no need to tell me.


'input code to find first cell
mailbody = ActiveCell.Value
For i = 1 To 10
'input code to find the other cells
mailbody = & Chr(10) & ActiveCell.Value
Next i

[Redants]

My apologies for not posting the code, I guess my question was as clear as mud eh?
Below is my code, this posts the active sheet to outlook with information in the main body of the email (item1 & item2). What I do not know is how to get it to look like this:

Item 1
Item 2

The items will be the same cells for the sheet, as this'l become a form that employees send in with a list of options they want for courses.
I know it must be blindingly obvious to anyone who knows about coding, but I can't seem to find out what I need to type anywhere!

Code:

Sub EmailWithOutlook()
Dim oApp As Object, _
oMail As Object, _
WB As Workbook, _
FileName As String

Application.ScreenUpdating = False

ActiveSheet.Copy
Set WB = ActiveWorkbook
FileName = "Reportbookname.xls"
On Error Resume Next
Kill "C:\" & FileName
On Error GoTo 0
WB.SaveAs FileName:="C:\" & FileName

MsgBox "Message"

Set oApp = CreateObject("Outlook.Application")
Set oMail = oApp.CreateItem(0)
With oMail
.To = "receipient@mail.com"
.Subject = "The report"

Dim EMailComment
EMailComment = ActiveSheet.Range("Item1").Value & ActiveSheet.Range("Item2").Value
.Body = EMailComment
.Attachments.Add WB.FullName
.Display
End With

WB.ChangeFileAccess Mode:=xlReadOnly
Kill WB.FullName
WB.Close SaveChanges:=False

Application.ScreenUpdating = True
Set oMail = Nothing
Set oApp = Nothing
End Sub


Thanks again, sorry for asking the simple questions, but i'm very green at this.

[Ago]

i cant get that code to work, but hey it doesnt matter. if it works on your machine its all good.

the answer to your question is still
/...

.Body = ActiveSheet.Range("Item1").Value & Chr(10) & ActiveSheet.Range("Item2").Value

..../

i think this would work, as i wrote earlier Chr(10) means enter. but i dont think you can first put it in the string EMailComment, i think you have to put it in .body

try it and see if it works

[Redants]

Thank you Ago, that was exactly what I needed! :-)

When I put the code up, I deleted the comments, here it is again hopefully it'll work for you this time. A small thank you for the little bit of code that has stopped me beating my head against the wall!

one more go:

Sub EmailWithOutlook()
'Variable declaration
Dim oApp As Object, _
oMail As Object, _
WB As Workbook, _
FileName As String

'Turn off screen updating
Application.ScreenUpdating = False

'Make a copy of the active sheet and save it to a temporary file
ActiveSheet.Copy
Set WB = ActiveWorkbook
FileName = "Reportbookname.xls"
On Error Resume Next
Kill "C:\" & FileName
On Error GoTo 0
WB.SaveAs FileName:="C:\" & FileName

MsgBox "Message"

'Create and show the outlook mail item
Set oApp = CreateObject("Outlook.Application")
Set oMail = oApp.CreateItem(0)
With oMail
'Uncomment the line below to hard code a recipient
'.To = "recepient@mail.com"
'Uncomment the line below to hard code a subject
'.Subject = "The report"

Dim EMailComment
EMailComment = ActiveSheet.Range("item1").Value & Chr(10) & ActiveSheet.Range("item2").Value
.Body = EMailComment
.Attachments.Add WB.FullName
.Display
End With

'Delete the temporary file
WB.ChangeFileAccess Mode:=xlReadOnly
Kill WB.FullName
WB.Close SaveChanges:=False

'Restore screen updating and release Outlook
Application.ScreenUpdating = True
Set oMail = Nothing
Set oApp = Nothing
End Sub



Thanks again, I owe you one!

[lucas]

Redants, you can mark your thread solved using the thread tools at the top of the page.

[Ago]

no problem!
i think its not working because i dont have outlook installed.
i chosed to keep outlook express, i dont like outlook 2003.