+ Reply to Thread
Results 1 to 13 of 13

Auto email from excel with custom subject

  1. #1
    Vikram
    Guest

    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


  2. #2
    Ron de Bruin
    Guest

    Re: 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" <vikram.palshikar@gmail.com> wrote in message news:1153725189.193038.197130@i42g2000cwa.googlegroups.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. #3
    Vikram
    Guest

    Re: 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. #4
    Vikram
    Guest

    Re: 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. #5
    Ron de Bruin
    Guest

    Re: 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 = "ron@debruin.nl"
    .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" <vikram.palshikar@gmail.com> wrote in message news:1153805061.586566.25370@p79g2000cwp.googlegroups.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. #6
    Vikram
    Guest

    Re: 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. #7
    Ron de Bruin
    Guest

    Re: 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" <vikram.palshikar@gmail.com> wrote in message news:1153864664.634043.198850@75g2000cwc.googlegroups.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. #8
    Vikram
    Guest

    Re: 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" <vikram.palshikar@gmail.com> wrote in message news:1153864664.634043.198850@75g2000cwc.googlegroups.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. #9
    Ron de Bruin
    Guest

    Re: 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" <vikram.palshikar@gmail.com> wrote in message news:1153953486.611657.54590@75g2000cwc.googlegroups.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" <vikram.palshikar@gmail.com> wrote in message news:1153864664.634043.198850@75g2000cwc.googlegroups.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. #10
    Vikram
    Guest

    Re: 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. #11
    Vikram
    Guest

    Re: 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. #12
    Vikram
    Guest

    Re: 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 = "vikram.palshikar@gmail.com"
    .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. #13
    Vikram
    Guest

    Re: 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 = "vikram.palshikar@gmail.com"
    > .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 to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1