Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Auto email from excel with custom subject
hi...i have a complaint database in excel. I have a userform to enter
complaints. I have written a code so that excel sends a notification email automatically asv soon as user clicks on the "Submit" button on the userform. I want to customise this email subject & body with complaint details. e.g. subject will say something like "Ä new complaint no 12345 has been logged for ABC trading Company". The complaint number is entered in colun A & Customer name is stored in column C of the worksheet "ComplaintData". I want the same also in the email body. ...something like : "Ä new complaint no 12345 has been logged for ABC trading Company. Please log on to Customer Complaint System to see details" Also, I am wondering if I can change the sender name on these auto-generated email...e.g. Ï would like the sender name to be "Customer Complaint system" instead of my own name or the user's name who is logging the complaint. Is this possible?? Cheers |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Auto email from excel with custom subject
Hi Vikram
Also, I am wondering if I can change the sender name Check out My CDO page http://www.rondebruin.nl/cdo.htm -- Regards Ron de Bruin http://www.rondebruin.nl "Vikram" wrote in message oups.com... hi...i have a complaint database in excel. I have a userform to enter complaints. I have written a code so that excel sends a notification email automatically asv soon as user clicks on the "Submit" button on the userform. I want to customise this email subject & body with complaint details. e.g. subject will say something like "Ä new complaint no 12345 has been logged for ABC trading Company". The complaint number is entered in colun A & Customer name is stored in column C of the worksheet "ComplaintData". I want the same also in the email body. ...something like : "Ä new complaint no 12345 has been logged for ABC trading Company. Please log on to Customer Complaint System to see details" Also, I am wondering if I can change the sender name on these auto-generated email...e.g. Ï would like the sender name to be "Customer Complaint system" instead of my own name or the user's name who is logging the complaint. Is this possible?? Cheers |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Auto email from excel with custom subject
Hi Ron...I tried the code on your website for sending small message.
But its giving me error saying "The "Sendusing" configuration value is invalid". I am very new at this. Can you please tell me where I need to put this code? I have added it to the command button, so that when user clicks on the button it should send email. cheers vikram Ron de Bruin wrote: Hi Vikram Also, I am wondering if I can change the sender name Check out My CDO page http://www.rondebruin.nl/cdo.htm -- Regards Ron de Bruin http://www.rondebruin.nl |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Auto email from excel with custom subject
Hi Ron
I have following code in the "submit" command button on my userform. It sends the email, but I want to be able to have custom subject & body to every email based on user input. e.g. on my userform, Complaint Number (which is auto generated) is shown on a label called "txtCompno" and Customer name is selected by user from a combobox called "txtCustomer". Both these change with every new complaint entry. I want every email to have both these specified in the subject & body so that the recipients can go back to database and search for that complaint number. I am not sure if I can use CDO.....I tried using it but didnt succeed for some reason. Would appreciate your help. Cheers Vikram ================================================== ==== Dim aOutlook As Object Dim aEmail As Object Dim rngeAddresses As Range, rngeCell As Range, strRecipients As String Set aOutlook = CreateObject("Outlook.Application") Set aEmail = aOutlook.CreateItem(0) 'set sheet to find address for e-mails as I have several people to mail to Set rngeAddresses = ActiveSheet.Range("B7:B7") For Each rngeCell In rngeAddresses.Cells strRecipients = strRecipients & ";" & rngeCell.Value Next 'set Importance aEmail.Importance = 2 'Set Subject aEmail.Subject = "A new complaint no " & Me.txtcompno.Caption & " is logged" 'Set Body for mail aEmail.Body = "Please log onto the Customer Complaint System to check Complaint no " & txtcompno.Caption & " for " & txtcustomer & " entered today." 'Set Recipient aEmail.To = strRecipients aEmail.Send ================================================== ============ |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Auto email from excel with custom subject
You can use a label or textbox like this in the code of a button on the userform
Private Sub CommandButton1_Click() Dim OutApp As Object Dim OutMail As Object Dim strbody As String Set OutApp = CreateObject("Outlook.Application") Set OutMail = OutApp.CreateItem(0) With OutMail .To = " .CC = "" .BCC = "" .Subject = Me.Label1.Caption .Body = Me.TextBox1.Text .display 'or use .Display End With Set OutMail = Nothing Set OutApp = Nothing Unload Me End Sub I am not sure if I can use CDO.....I tried using it but didnt succeed for some reason. Have you try to use the commented blue code lines in the examples ?? -- Regards Ron de Bruin http://www.rondebruin.nl "Vikram" wrote in message ups.com... Hi Ron I have following code in the "submit" command button on my userform. It sends the email, but I want to be able to have custom subject & body to every email based on user input. e.g. on my userform, Complaint Number (which is auto generated) is shown on a label called "txtCompno" and Customer name is selected by user from a combobox called "txtCustomer". Both these change with every new complaint entry. I want every email to have both these specified in the subject & body so that the recipients can go back to database and search for that complaint number. I am not sure if I can use CDO.....I tried using it but didnt succeed for some reason. Would appreciate your help. Cheers Vikram ================================================== ==== Dim aOutlook As Object Dim aEmail As Object Dim rngeAddresses As Range, rngeCell As Range, strRecipients As String Set aOutlook = CreateObject("Outlook.Application") Set aEmail = aOutlook.CreateItem(0) 'set sheet to find address for e-mails as I have several people to mail to Set rngeAddresses = ActiveSheet.Range("B7:B7") For Each rngeCell In rngeAddresses.Cells strRecipients = strRecipients & ";" & rngeCell.Value Next 'set Importance aEmail.Importance = 2 'Set Subject aEmail.Subject = "A new complaint no " & Me.txtcompno.Caption & " is logged" 'Set Body for mail aEmail.Body = "Please log onto the Customer Complaint System to check Complaint no " & txtcompno.Caption & " for " & txtcustomer & " entered today." 'Set Recipient aEmail.To = strRecipients aEmail.Send ================================================== ============ |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Auto email from excel with custom subject
Hi Ron
I used your code above. I changed the lable & textbox name to suit my userform. For some reason my subject and body of the email is coming blank. If I have just one default subject and body then it works fine, but doesnt allow me to pick current details from userform fields. any suggestions?? I have referenced Outlook Object library. Is there anything else I need to do in the set up of the worksheet/workbook? cheers |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Auto email from excel with custom subject
Send me your test workbook private and I look at it
-- Regards Ron de Bruin http://www.rondebruin.nl "Vikram" wrote in message ups.com... Hi Ron I used your code above. I changed the lable & textbox name to suit my userform. For some reason my subject and body of the email is coming blank. If I have just one default subject and body then it works fine, but doesnt allow me to pick current details from userform fields. any suggestions?? I have referenced Outlook Object library. Is there anything else I need to do in the set up of the worksheet/workbook? cheers |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Auto email from excel with custom subject
Hi Ron
I have emailed you my file. I would appreciate if you can look at it for me. regards vikram Ron de Bruin wrote: Send me your test workbook private and I look at it -- Regards Ron de Bruin http://www.rondebruin.nl "Vikram" wrote in message ups.com... Hi Ron I used your code above. I changed the lable & textbox name to suit my userform. For some reason my subject and body of the email is coming blank. If I have just one default subject and body then it works fine, but doesnt allow me to pick current details from userform fields. any suggestions?? I have referenced Outlook Object library. Is there anything else I need to do in the set up of the worksheet/workbook? cheers |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Auto email from excel with custom subject
Look at tomorrow after work Bed time for me -- Regards Ron de Bruin http://www.rondebruin.nl "Vikram" wrote in message ps.com... Hi Ron I have emailed you my file. I would appreciate if you can look at it for me. regards vikram Ron de Bruin wrote: Send me your test workbook private and I look at it -- Regards Ron de Bruin http://www.rondebruin.nl "Vikram" wrote in message ups.com... Hi Ron I used your code above. I changed the lable & textbox name to suit my userform. For some reason my subject and body of the email is coming blank. If I have just one default subject and body then it works fine, but doesnt allow me to pick current details from userform fields. any suggestions?? I have referenced Outlook Object library. Is there anything else I need to do in the set up of the worksheet/workbook? cheers |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Auto email from excel with custom subject
Hi Ron
thanks for your email. I have checked that sample file and it works beautifully. For some reason I couldn't make it work in my file...but will try again... Can I change the "Sender name" without using CDO ? Do I need accesss to a webserver to use CDO ? as I do not have access to web server..... cheers Vikram |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
Auto email from excel with custom subject
Hi Ron
I tried your sample code. It works in your file, but for some reason it doesnt work when I add it to my file. I am wondering if some code in my file is interacting with your code? Also, in your example, it picks up text added to the textbox. In my file I want it to pick up values from 1) a label called "txtcompno" (this value is complaint number which is auto-generated and increments everytime user opens the form...90001 , 90002 , 90003...and so on..) 2) a combobox called "txtcustomer". Row source of this combobox is a customerlist which is in a hidden sheet. I modified your code as below to achieve this: ..Subject = "A new Complaint No." & Me.txtcompno.caption & " has been entered." ..Body = "A new Complaint No." & Me.txtcompno.caption & " has been entered for" & Me.txtcustomer.value but for some reason its not working. When the email page is opened, it only shows the default text with blanks. I am using following code to auto-generate the complaint number which is displayed on the label "txtcompno". After doing modification in your code, its also giving me error in the code below: Private Sub UserForm_Activate() Me.txtdate.Caption = Format(Now(), "dd/mm/yyyy") Me.txtcompno.Enabled = True Dim iRow As Long Dim ws As Worksheet Set ws = Worksheets("ComplaintData") 'find last data row from database iRow = ws.Cells(Rows.Count, 1) _ ..End(xlUp).Row If ws.[A2].Value = "" Then Me.txtcompno.Caption = 90001 Else Me.txtcompno.Caption = ws.Cells(iRow, 1).Value + 1 End If End Sub Any idea why its doing it?? regards vikram ================================================== ============= Vikram wrote: Hi Ron thanks for your email. I have checked that sample file and it works beautifully. For some reason I couldn't make it work in my file...but will try again... Can I change the "Sender name" without using CDO ? Do I need accesss to a webserver to use CDO ? as I do not have access to web server..... cheers Vikram |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
Auto email from excel with custom subject
Hi Ron
I just figured out that, this works if I create a separate commandbutton on the userform to send email. But as soon as I add this code to the additem code (shown below) it starts playing up. It opens email page fine, but the subject goes blank...and only shows default text. Is there any way, I could use a checkbox or something that will allow me to do both the things : 1. Add record to the database 2) send email with custom subject, with one commnd button instead of two? cheers ================================================== =========== Private Sub cmdAdd_Click() Dim iRow As Long Dim ws As Worksheet Set ws = Worksheets("ComplaintData") 'find first empty row in database iRow = ws.Cells(Rows.Count, 1) _ .End(xlUp).Offset(1, 0).Row 'copy the data to the database ws.Cells(iRow, 1).Value = Me.txtcompno.Caption ws.Cells(iRow, 2).Value = Me.txtdate.Caption ws.Cells(iRow, 3).Value = Me.txtcustomer.Value ws.Cells(iRow, 4).Value = Me.txtconper.Value ws.Cells(iRow, 5).Value = Me.txtproduct.Value ws.Cells(iRow, 6).Value = Me.txtbatch.Value ws.Cells(iRow, 7).Value = Me.txtcategory.Value ws.Cells(iRow, 8).Value = Me.txtdescription.Value ws.Cells(iRow, 9).Value = Me.txtAM.Value 'clear the data Me.txtcompno.Caption = "" Me.txtcustomer.Value = "" Me.txtconper.Value = "" Me.txtproduct.Value = "" Me.txtbatch.Value = "" Me.txtcategory.Value = "" Me.txtdescription.Value = "" Me.txtAM.Value = "" Dim OutApp As Object Dim OutMail As Object Dim strbody As String Set OutApp = CreateObject("Outlook.Application") Set OutMail = OutApp.CreateItem(0) With OutMail .To = " .CC = "" .BCC = "" .Subject = "A new Complaint No." & Me.txtcompno.Caption & " has been entered." .Body = "A new Complaint No." & Me.txtcompno.Caption & " has been entered for " & Me.txtcustomer.Value .display 'or use .Display End With Set OutMail = Nothing Set OutApp = Nothing Unload Me end sub ================================================== =========== |
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
Auto email from excel with custom subject
Hi....finally I managed to find what was wrong......In my own code, I
was trying clear the cells myself.....and then wondering why the fields are coming blank.... Me.txtcompno.Caption = "" Me.txtcustomer.Value = "" I removed above 2 lines from my code and now it works like dream! thanks ron for your patience and help! I am still looking for suggestions on the "Sender Name" problem. Cheers Vikram wrote: Hi Ron I just figured out that, this works if I create a separate commandbutton on the userform to send email. But as soon as I add this code to the additem code (shown below) it starts playing up. It opens email page fine, but the subject goes blank...and only shows default text. Is there any way, I could use a checkbox or something that will allow me to do both the things : 1. Add record to the database 2) send email with custom subject, with one commnd button instead of two? cheers ================================================== =========== Private Sub cmdAdd_Click() Dim iRow As Long Dim ws As Worksheet Set ws = Worksheets("ComplaintData") 'find first empty row in database iRow = ws.Cells(Rows.Count, 1) _ .End(xlUp).Offset(1, 0).Row 'copy the data to the database ws.Cells(iRow, 1).Value = Me.txtcompno.Caption ws.Cells(iRow, 2).Value = Me.txtdate.Caption ws.Cells(iRow, 3).Value = Me.txtcustomer.Value ws.Cells(iRow, 4).Value = Me.txtconper.Value ws.Cells(iRow, 5).Value = Me.txtproduct.Value ws.Cells(iRow, 6).Value = Me.txtbatch.Value ws.Cells(iRow, 7).Value = Me.txtcategory.Value ws.Cells(iRow, 8).Value = Me.txtdescription.Value ws.Cells(iRow, 9).Value = Me.txtAM.Value 'clear the data Me.txtcompno.Caption = "" Me.txtcustomer.Value = "" Me.txtconper.Value = "" Me.txtproduct.Value = "" Me.txtbatch.Value = "" Me.txtcategory.Value = "" Me.txtdescription.Value = "" Me.txtAM.Value = "" Dim OutApp As Object Dim OutMail As Object Dim strbody As String Set OutApp = CreateObject("Outlook.Application") Set OutMail = OutApp.CreateItem(0) With OutMail .To = " .CC = "" .BCC = "" .Subject = "A new Complaint No." & Me.txtcompno.Caption & " has been entered." .Body = "A new Complaint No." & Me.txtcompno.Caption & " has been entered for " & Me.txtcustomer.Value .display 'or use .Display End With Set OutMail = Nothing Set OutApp = Nothing Unload Me end sub ================================================== =========== |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Stop excel from dropping the 0 in the beginning of a number? | Setting up and Configuration of Excel | |||
Excel 2007 Custom Error Bars | Excel Discussion (Misc queries) | |||
Custom error bars excel 2007 | Charts and Charting in Excel | |||
Excel email address routing | Excel Discussion (Misc queries) | |||
Can you enable auto complete from a custom list in Excel? | Excel Discussion (Misc queries) |