Email 1 Cell w/VBA
 

Trying to get the information in 1 cell to show up
in the body of an email.

I have a VBA userform that enters tasks into a spreadsheet. It also
emails the information from that form to the person that has the
responsibility of assigning these tasks. The form also assigns an Entry
Number to each task in the spreadsheet. Since this information is in
the spreadsheet and not the form, how do I get it to email in the body
of the email along with the information from the form.

Any help with this would be greatly appreciated!

The code I am using to create the Entry Number is:

' Creat Record number
NextRow = Range("A65536").End(xlUp).Row
Cells(NextRow, 15) = NextRow - 1

The code I am using to email the information from the form is:

Dim OutApp As Outlook.Application
Dim OutMail As Outlook.MailItem
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(olMailItem)
With OutMail

..To = "
..CC = ""
..BCC = ""
..Subject = "B2B Tracking-New Task (TEST-PLEASE IGNORE)"
..Body = "Associate taking the request:" & vbCrLf &
NewTaskForm.AsscNameTxt1.Value & vbNewLine & vbNewLine & _
"Date Request Received:" & vbCrLf & NewTaskForm.DateRecTxt1.Value &
vbNewLine & vbNewLine & _
"Date Request Promised:" & vbCrLf & NewTaskForm.DateExpTxt1.Value &
vbNewLine & vbNewLine & _
"Person Requesting Job:" & vbCrLf & NewTaskForm.RequestNameTxt1.Value &
vbNewLine & vbNewLine & _
"Contact Number:" & vbCrLf & NewTaskForm.ContactNumberTxt1.Value &
vbNewLine & vbNewLine & _
"Email Address:" & vbCrLf & NewTaskForm.EmailTxt1.Value & vbNewLine &
vbNewLine & _
"Name On Account:" & vbCrLf & NewTaskForm.AcctNameTxt1.Value &
vbNewLine & vbNewLine & _
"Number of Mobiles:" & vbCrLf & NewTaskForm.MobilesTxt1.Value &
vbNewLine & vbNewLine & _
"Account Number:" & vbCrLf & NewTaskForm.AcctNumberTxt1.Value &
vbNewLine & vbNewLine & _
"Market:" & vbCrLf & NewTaskForm.ComboMarkets1.Value & vbNewLine &
vbNewLine & _
"Details of Request:" & vbCrLf & NewTaskForm.DetailsTxt1.Value



..Send
End With
Set OutMail = Nothing
Set OutApp = Nothing


---
Message posted from http://www.ExcelForum.com/

Email 1 Cell w/VBA
 

consult Ron de Bruin's site

http://www.rondebruin.nl/sendmail.htm
http://www.rondebruin.nl/index.html

an Article by Ron on MSDN:
http://msdn.microsoft.com/library/en...odc_xlmail.asp


--
Regards,
Tom Ogilvy

rayzgurl wrote in message
...
Trying to get the information in 1 cell to show up
in the body of an email.

I have a VBA userform that enters tasks into a spreadsheet. It also
emails the information from that form to the person that has the
responsibility of assigning these tasks. The form also assigns an Entry
Number to each task in the spreadsheet. Since this information is in
the spreadsheet and not the form, how do I get it to email in the body
of the email along with the information from the form.

Any help with this would be greatly appreciated!

The code I am using to create the Entry Number is:

' Creat Record number
NextRow = Range("A65536").End(xlUp).Row
Cells(NextRow, 15) = NextRow - 1

The code I am using to email the information from the form is:

Dim OutApp As Outlook.Application
Dim OutMail As Outlook.MailItem
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(olMailItem)
With OutMail

To = "
CC = ""
BCC = ""
Subject = "B2B Tracking-New Task (TEST-PLEASE IGNORE)"
Body = "Associate taking the request:" & vbCrLf &
NewTaskForm.AsscNameTxt1.Value & vbNewLine & vbNewLine & _
"Date Request Received:" & vbCrLf & NewTaskForm.DateRecTxt1.Value &
vbNewLine & vbNewLine & _
"Date Request Promised:" & vbCrLf & NewTaskForm.DateExpTxt1.Value &
vbNewLine & vbNewLine & _
"Person Requesting Job:" & vbCrLf & NewTaskForm.RequestNameTxt1.Value &
vbNewLine & vbNewLine & _
"Contact Number:" & vbCrLf & NewTaskForm.ContactNumberTxt1.Value &
vbNewLine & vbNewLine & _
"Email Address:" & vbCrLf & NewTaskForm.EmailTxt1.Value & vbNewLine &
vbNewLine & _
"Name On Account:" & vbCrLf & NewTaskForm.AcctNameTxt1.Value &
vbNewLine & vbNewLine & _
"Number of Mobiles:" & vbCrLf & NewTaskForm.MobilesTxt1.Value &
vbNewLine & vbNewLine & _
"Account Number:" & vbCrLf & NewTaskForm.AcctNumberTxt1.Value &
vbNewLine & vbNewLine & _
"Market:" & vbCrLf & NewTaskForm.ComboMarkets1.Value & vbNewLine &
vbNewLine & _
"Details of Request:" & vbCrLf & NewTaskForm.DetailsTxt1.Value



Send
End With
Set OutMail = Nothing
Set OutApp = Nothing


---
Message posted from http://www.ExcelForum.com/

Email 1 Cell w/VBA
 

Thanks for the reply Tom.

I have already checked out this site. I do not need to email a range o
cells, just the information out of 1 cell. Since this is the Entr
Number, or Invoice Number for each entry, the cell would change wit
every entry, and would not be constant

--
Message posted from http://www.ExcelForum.com

Email 1 Cell w/VBA
 

Add code like:
NextRow = Range("A65536").End(xlUp).Row
' guessing you need to subtract 1 - you know your
' sheet better than I do (and when things change)
EntryNumber = Cells(NextRow-1, 15).Value

In your body string, just conatenate it in

string & " " & entrynumber & " " & string

--
regards,
Tom Ogilvy


rayzgurl wrote in message
...
Thanks for the reply Tom.

I have already checked out this site. I do not need to email a range of
cells, just the information out of 1 cell. Since this is the Entry
Number, or Invoice Number for each entry, the cell would change with
every entry, and would not be constant.


---
Message posted from http://www.ExcelForum.com/

Email 1 Cell w/VBA
 

Thanks a bunch Tom!

You ROCK!!!!:cool

--
Message posted from http://www.ExcelForum.com