Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Email 1 Cell w/VBA
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using Macro how to create email link for the email addresses in aRange or Selection | Excel Worksheet Functions | |||
send wkbk as an email attachment with an email address copied from | Excel Discussion (Misc queries) | |||
can I copy a column of email addresses, paste into email address? | New Users to Excel | |||
Email editor closes when forwarding Excel-embedded email | Setting up and Configuration of Excel | |||
email cell | Excel Programming |