Sending emails via Excel 2007

[nobleprince]

Hi,

I have data in columns A3 to R3, etc. I want to be able to send emails from Excel 2007 via Outlook 2007. Also there are requirements to be able to select all, remove duplicates, and reset checkboxes (rows) for emails. There are 1000 records.

I have attached a sample spreadsheet. Please see sheet1 and sheet2 within the spreadsheet.

Can anyone please assist?

Regards!
Ash

[jaslake]

Hi Ash
There is no file attached to your post.
That aside, with data in columns A thru R, what constitutes a duplicate? When you do attach the sample file, include a duplicate and highlight it so we know what you designate as duplicates.
John

[nobleprince]

File attached now.

[jaslake]

Hi Ash

Try the attached. Add your signature directly into Outlook then set the reference in the send email procedure. I've left my reference in the code so you can see where it goes.

In the procedure I personally use, I've set the Email Subject and the Email Body up in a UserForm. You may wish to consider that rather than using a worksheet.

You'll notice I made some changes to your worksheet (such as eliminating the check boxes). Examine the code and you'll see what it does.

If you need further help, let me know. Also let me know of issues.

John

[nobleprince]

Hi John,

A couple of things:

1) Is it possible to have the ability to be able to "check" each row as desired and then when the Send button is clicked it only sends the emails to those which are checked.

2) At present, when new entries are added to Sheet1, the Select All function does not “select” the new entries. The same applies for the Reset button, it does not deselect (all) new entries.

3) When the Send button is pressed, within the email the forename is grabbed from A3 for all the emails, as opposed to grabbing each unique forename from A3, B3, C3, etc.

4) The duplicate function overwrites duplicate entries over itself. Each duplicate entry should appear in new row.

5) Is it possible to have the duplicate output exactly the same format as Sheet1?

6) I have added the signature directly into Outlook then set the reference in the send email procedure but I get MS Visual Basic error: "Compile error: Sub or Function not defined".
See attached two screen dumps. I have replaced the XXX in place of the <username> only during the screen dumps.

7) Would it be possible to see how you’ve setup the Email Subject and the Email Body up in a UserForm? Could you email the UserForm / spreadsheet?

Your help is much appreciated...

[jaslake]

Hi Ash
This capability is already in the procedure

1) Is it possible to have the ability to be able to "check" each row as desired and then when the Send button is clicked it only sends the emails to those which are checked

Simply "Double Click" on the item you wish to mark.

As for the other issues, I'll look at them and get back to you.

John

[jaslake]

Hi Ash

Regarding this

6) I have added the signature directly into Outlook then set the reference in the send email procedure but I get MS Visual Basic error: "Compile error: Sub or Function not defined".

Your code is halting here

Please Login or Register  to view this content.

This code is at the bottom of Module 3

Please Login or Register  to view this content.

Make certain it's not commented out. The procedure isn't finding this function.
That SHOULD fix this issue (assuming your path reference is correct).

I've fixed the other issues but want to test them. I'll send you an updated file either this PM or in the AM.

John

[jaslake]

Hi Ash

I've attached two files and they both address all of the issues you raised except perhaps

6) I have added the signature directly into Outlook then set the reference in the send email procedure but I get MS Visual Basic error: "Compile error: Sub or Function not defined".

The files differ in that one uses a worksheet (as did your original) for Subject/Body data for the email and the other uses a UserForm for the Subject/Body.

Play with them and let me know of issues.

John

[nobleprince]

Hi John,

Getting almost there!

I'm using the Sample1.4 with UserForm.xlsm

Just one issue I can see when the Send button is pressed the email(s) are sent from row J as opposed to Row G. The primary ("Too..") email should be picked up from Row G.

TIA

[nobleprince]

Hi John,

Just noticed, a few more issues:

1a) When the duplicate button is pressed it does not remove the A3 column as a duplicate if there are multiple instances of it (A3 columns). However the remaining columns B3, C3, etc, seem to be ok.

2a) Also, when there are multiple instances of duplicate columns. The original information is not maintained from row E onwards. Please see attached snapshots of before and after for point 2a.

TIA

[jaslake]

Hi Ash

Regarding this

email(s) are sent from row J as opposed to Row G.

I caught this when I developed the "With UserForm" workbook. I believe you'll find that workbook does indeed pick up the correct "Send To" email address. If I'm wrong, please let me know.

I did fail to go back and make the correction in the "Without UserForm" workbook. If you change one line of code in
Sub Create_Mail() from

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

that issue should be resolved.

Regarding your before and after jpg's, that's a function of how the data was entered. I'm going to change the procedure so it ALWAYS keeps the FIRST item of duplicate items, such that, if the FIRST item has complete information, it'll be preserved. If the information is incomplete on the FIRST item, there's really not much that can be done.

Regarding this

1a) When the duplicate button is pressed it does not remove the A3 column as a duplicate if there are multiple instances of it (A3 columns). However the remaining columns B3, C3, etc, seem to be ok

I don't understand. Is this related to your before and after jpg's? If not, please post an example.

I'll get back to you.

John

[jaslake]

Hi Ash


I believe the attached file handles the following:

1. Send To comes from Column G
2. The earliest duplicate record is maintained on Sheet 1; all other duplicate records are moved to the Dups worksheet

I'm still confused by this

1a) When the duplicate button is pressed it does not remove the A3 column as a duplicate if there are multiple instances of it (A3 columns). However the remaining columns B3, C3, etc, seem to be ok

I've only updated the "With UserForm" workbook. If you wish to update the "Without UserForm" workbook, I can do so or you can. If you wish to do so, simply replace the ENTIRE module2 procedures in "Without UserForm" workbook with the ENTIRE module2 procedures from the "With UserForm" workbook (copy/paste).

Let me know of issues.

John

[nobleprince]

Hi John,

Sample1.6 with UserForm.xlsm seems to work better.

1.1) We can ignore the point for 1a. It seems to have been resolved, although the A3 row in the dups worksheet seems to be inconsistently displaying compared to the other remaining rows, i.e. first name loses it's hyper link and emails are displayed in italic.


1.2) When the duplicates are ran twice, the previous duplicates get overwritten? Also the duplicates do not appear in the same order they were "moved" to dups. Ideally they should appear in the same order or displayed alphabetically.

Regards,
Ash

[jaslake]

Hi Ash

By design, worksheet Dups is recreated each time the procedure to eliminate dups is run. Is it your desire to have it cumulative?

Regarding this

duplicates do not appear in the same order they were "moved" to dups

Actually they do appear in the same order as they were moved. They're being moved from the bottom of Sheet1 up to the top (if you delete rows from the top down, VBA gets confused).

I'll need to think about this. I'd assume it can be done, possibly with an array. I'll look into it.

This has been resolved

the A3 row in the dups worksheet seems to be inconsistently displaying

Back to sheet Dups...should all data be maintained?

John

[nobleprince]

Hi John,

Is it your desire to have it cumulative?
Yes

Back to sheet Dups...should all data be maintained?
Yes

[jaslake]

Hi Ash


The attached, I believe, resolves the issues we discussed:

1. Sheet Dups data is maintained
2. Sheet Dups data is listed top to bottom, not bottom to top
3. Sheet Dups data is consistently displayed (see note)

note...formatting is being copied from Sheet1 to Sheet Dups. If you have more items in Sheet Dups than you have in Sheet1, the hyperlinks on Sheet Dups will not be created. Simply copy the formatting down and they will be displayed. Alternately, pre-format Column A on Sheet Dups down to a reasonable number of rows. I've done so down to row 25.

Let me know of issues.

John

[jeth]

I have a spreadsheet that is available for others use, and I know that sensitive areas and formulas can be protected and hidden.

But I would like to get a notice (email preferably) when someone opens the spreadsheet, that would identify the user and date/time of the access. This way I could tell if anyone other than those authorized is even trying to do something with it.

Password protection for the entire sheet wouldn't really be useful because people tend to pass them around like Halloween candy.

Thanks

Hmmm. I thought "New Post" would create a new thread, but apparently not. How do I create one?

[jaslake]

Hi Jeth

Hmmm. I thought "New Post" would create a new thread, but apparently not. How do I create one?

There's a big red button at the top and at the bottom of the Excel Programming page. Click it.

John

[jeth]

Thanks, John,

I have just posted my question...

Joe