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

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

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

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
================================================== ============

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
================================================== ============

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