Email Sending Userform problem

[emina002]

I am building a Userform where the user can freely compose email he/she wants,

I have a list box listed some field(source is from Sheet1 from Column C so on.. and I already set name range to it), to visualize my problem kindly download first the workbook attach within this thread.

From opening the workbook Sheet1 already visible with button Compose email, as you click that userform will be visible.

This workbook is sample only of my problem and not the really exact file(so sorry for the field and item name), it is more replicate of Mail Merge on word.


My problem is to code the ff. because I dont know how to start.


1. Is if the user type "Hello" followed by drag the column field "Name" listed on listbox supposed to be the value of Textbox now is "Hello Name", (I already did this but checked if I code it correctly)


2. When I check "Preview" it will change the value of "Hello Name1"(this is the first line item or record) on textbox and the "<<" ">>" button is previous and next record so when i press ">>" button it will show "Hello Name2" and so on, same with << button it will show the previous line item.(dont know how to start writing a code to call all item on Sheet1)

3. Previewing the item and next and previous button will be applicable also to TO and CC textbox field.(need also help on this)


I did this so the user can freely choose the field item he want to include when composing email. I already set a name range of the column header so the user can add many column header when needed before start composing email.


The purpose of this is to send bulk of email in one click with the standard email compose by the user. User can used mail merge on word but no CC recipient field available, and also ease for the user to add or delete line item not to include on sending emails.

Hope you can help, feel free to ask for additional question, I hope I explained it well. Thanks in advance

[gmayor]

It might be simpler if you use http://www.gmayor.com/ManyToOne.htm in One to One mode, which will allow you to use CC (and BCC) to merge to e-mail.

The process you envisage is not going to work as you have it. You need to create a separate window for the preview. I would probably use a label, then set its size and position to the size of the message text box and then hide the text box when you click Preview. The label caption would initially display the message content and then replace the keywords in the caption text with the data from the worksheet. Your message text would need to have the keywords identified e.g. <<keyword>> so add the chevrons as you apply them.

When you uncheck preview the label can be moved off the form and the text box restored.

That should keep you amused for a few hours.

[snb]

I don't like bulk email ( we already get too much of those), neither do I like bulk code:

Private Sub UserForm_Initialize()
   ListBox1.Column = Range("EmailField").Value
End Sub

[gmayor]

I thought it might be amusing to play around with the userform, and while if I was going where you are headed I wouldn't have started from this point, I think the attached is close to what you had in mind. The two stage process is not something I would normally have considered. It could have been better to simply read the worksheet into a multicolumn list box and pick the record you want, populating the form directly from the values in the (hidden) columns of the list box.

[emina002]

Hello @Gmayor, this is actually what I need. I don't know how to thank you enough .

[gmayor]

It's been a very hot day here today - too hot to go out - so I have amused myself playing around further with your project and you may find the attached alternative approach a useful step off point.

[emina002]

I am very amazed on what you done to my project. Last question is there a way I can highlight the field like when transferring to outlook like formatting into bold letters so the receiver will notice important details (example Amount field)? Thanks

[gmayor]

You can't have mixed bold and normal text in a userform text box or label. In order to add bold text to the field data you would have to add a tag to the data/field, and then when it is written to the message body, perform a search and replace for the tags e.g. in the cmd_Drag code

Private Sub cmd_Drag_Click()
Dim strText As String
Dim i As Long
    For i = 0 To ListFields.ListCount - 1
        If ListFields.Selected(i) Then
            strText = strText & "[" & ListFields.List(i) & "]"
            TextMessage.Text = TextMessage.Text & strText
        End If
    Next
lbl_Exit:
    Exit Sub
End Sub

and in the send and draft codes you would need to locate the line

oRng.Text = TextMessage.Text

and add after it

With oRng.Find
            Do While .Execute(FindText:="\[*\]", MatchWildcards:=True)
                oRng.Text = Replace(oRng.Text, "[", "")
                oRng.Text = Replace(oRng.Text, "]", "")
                oRng.Font.Bold = True
                oRng.Collapse 0
            Loop
        End With

to remove the tags and add the bold text.

[emina002]

That is remarkable tips I am thinking to include html tag and include it and on send email button include .htmlbody instead if .body

Anyway thank you for your help, your my hero!

[gmayor]

The method I have used to write to the message does not involve html. It uses the Word editor object to process the message, much as you would edit the message directly in Outlook. This has the advantage of retaining the default signature. You can create a message using HTML if you prefer, but not with the code I have suggested.

[emina002]

Hello I tried the tool on other MS Version, it works well on MS Excel 2007 but when I run the userform on MS Excel 2010 its seems creating outlook message didn't loop on required data listed on sheet1.

[emina002]

Ok Gmayor, I already include the trick to send multiple emails in one click. But I noticed the amount seems to be not formatted in thousand or decimal format. I attempt to add keyword on the code but then when I add the dates it appear also in thousand format. is there any you can suggest? Thanks

[emina002]

@Gmayor, its me again, I am having a problem with amount and dates. I want to copy exact format on workbook that will be listed on textbox including on previewing the data, is there any other way? Thank you