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=2Eg. subject will say something like "=C4 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 : "=C4 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. =CF 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" <[email protected]> wrote in message news:[email protected]...
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 = "[email protected]"
.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" <[email protected]> wrote in message news:[email protected]...
> 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

Send me your test workbook private and I look at it

--
Regards Ron de Bruin
http://www.rondebruin.nl



"Vikram" <[email protected]> wrote in message news:[email protected]...
> 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
>

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" <[email protected]> wrote in message news:[email protected]...
> > 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
> >

Look at tomorrow after work

Bed time for me


--
Regards Ron de Bruin
http://www.rondebruin.nl



"Vikram" <[email protected]> wrote in message news:[email protected]...
> 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" <[email protected]> wrote in message news:[email protected]...
>> > 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
>> >
>

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

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

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 = "[email protected]"
.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
=============================================================

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 = "[email protected]"
> .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
> =============================================================