Auto email from excel with custom subject

[Vikram]

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

 

[Ron de Bruin]

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

 

[Vikram]

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

 

[Vikram]

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

 

[Ron de Bruin]

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 = "(e-mail address removed)"
.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 ??

 

[Vikram]

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

 

[Ron de Bruin]

Send me your test workbook private and I look at it

 

[Vikram]

Hi Ron

I have emailed you my file. I would appreciate if you can look at it
for me.

regards

vikram

 

[Ron de Bruin]

Look at tomorrow after work

Bed time for me

 

[Vikram]

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

 

[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]

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 = "(e-mail address removed)"
.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
=============================================================

 

[Vikram]

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