Macros and Programming

Hi,

I have read the help files and tried to do this myself with no joy. Does anyone have a simple macro that will allow me to choose an attachment when I send a bulk email?

I would be extremely grateful.

Steven

Hi,

Please record a macro with all settings YOU use (test it on one record with an email address to yourself).
The attachment is then not yet present.
Please copy your macro text and paste it in this thread.

I will look to insert a command for a dialog choosing an attachment (all users same attachment?).

regards
bert

Hi bert,

Thank you for that. After discussions with a colleague, I think it would be easier to use a macro that just inserts the email address from a field into the 'to' box of an Outlook message.

Is this possible?

Please ignore the last message, this does not work because of issues with maximum recipicents.

I am using the macro below. I get a box to choose a document to attach, and then get the error:

"Command Failed. Cannot find the specified file (80070002). Error in Line 11, character 2)" which is the beginning of the attach file command of this macro:

 ' For ways in which you can modify this macro,
 ' open the Tools > Bulk Email command and hit F1 for online help.

SendEmail20110803_3287c99 Records,RecordPosition,Records.Count ' Send email (automatically generated)

Sub SendEmail20110803_3287c99(recs,first,last) ' (automatically generated command)
 Dim oldStatus : oldStatus=StatusText
 StatusText="Sending email..."
 Dim sender
 Set sender=EmailSender
 sender.AttachFile x=GetOpenFilename
 sender.Subject="Test"
 sender.Message="Test email," & vbCrLf & vbCrLf & "Regards," & vbCrLf & vbCrLf & "Steven"
 Dim recsPreload(50),lastPreload : lastPreload=0

 Dim i,rec,text
 For i=first To last
  If i>lastPreload Then
    lastPreload=i-1+50 : If lastPreload>last Then lastPreload=last
    Dim j
    For j=i To lastPreload : Set recsPreload(lastPreload+1-j)=recs(j) : Next
    For j=i To lastPreload : recsPreload(lastPreload+1-j).Preload("EMAIL") : Next
    End If
  Set rec=recs(i)
  StatusText=rec.Fields("EMAIL")
  sender.AddRecipient rec.Fields("EMAIL")
  Next
  sender.Send ,False
 StatusText=oldStatus
 End Sub ' End of automatically generated SendEmail20110803_3287c99

Sorry to bombard you.

I have changed the macro so that I specify the specific files to attach within the macro, and that works. But it is a very messy way of sending a bulk email, because I would have to edit the macro everytime to change the body of the text and attached files (made especially painstaking with the way I seem to have to use "& vbCrLf & vbCrLf &" to donate a new line.

What I basically want to be able to do is use the bulk mail feature (because it knows the limit of email addresses per message and works around this automatically), but for it to open up outlook with all the email address in the bcc field so I can them compose my message and send it as normal.

Is this possible?

P.S: I also want to change the horrible font that it seems to send these emails in.

For good understanding let me turn it around:

1. You want compose a email in Outlook
2. You want to fill bcc field with a lot of email addresses which are in the current selection of Cardbox.
Right?

If this is all:
You could do this with a macro which picks up every email address in your record selection and place into Outlook's BCC field.
To avoid difficulties with Outlook, the easiest way is to copy all email addresses from the selection to the Clipboard, then paste it into BCC.
It is a pity that you cannot copy the addresses by Listindex because the dots and @ are standard not indexed in Cardbox.
So a simple for/next macro can collect the addresses from every record and place it on the clipboard then. You only has to paste it then.
This is a way of working a often use for not personalised email.

If you want to personalise email then a more complex macro is needed to generate the individual emails. I found a workaround for also sending the email in Outlook (not only composing) by the macro without the annoying Outlook security messages.

Bulk email in Cardbox is only generating txt email. It is not possible to generate HTML email which gives a lot of layout possibilities.
that is why I always use macro's generating email using Cardbox which can generate HTML email (even with images/logo's into your mail).

One of my future wishes for Cardbox is that a format with merge blocks and images etc could be used as source for generating simple HTML email. This cannot be very complicated difficult to add (some things are already present link end of line translation to <br>).
But I think this will ever be a wish...

Regards
Bert

Yes, you have it right. I do not need to generate individual emails.

My only issue with doing it via outlook is that I have a limit of 40 recipicents, otherwise I get an error. From reading the help, I know I can tell the Bulk Email tool to send it in batches of 40.

If I just copy and paste email addresses, I will lose that function.

I need a way of sending from outlook, but taking advantage of the recipicent number tool. Without having to split my email addressed manually into blocks of 40.

Several solutions are possible.
1. A vba macro in Outlook. Pickup 40 addresses in Cardbox, add it to bcc of current email and save body of current email. Repeat it again until... Not my favourite: must go around all macro security of Outlook.
2. The Clipboard way as I described. You can Cardbox let pause when 40 email addresses are copied to Clipboard. Then paste and send. Then Continue the Cardbox macro for next 40, paste and send etc etc. Is not difficult I think.
3. If you accept a simple layout, use Cardbox macro you recorded/send. Change anywhere you find 50 into 39. Your 40 limit is then safe.
4. My last bulk email I did I developed it did all with a Cardbox/smtp macro.
- The message subject + text is in the current format. I added markers for subject and body text.
- For bulk mail you do not need field in it. However, a format needs one field. So I add a field which does not contain data in this format.
- That message text is then written to the temp folder and then by the macro to html text.
- Macro reads the html data (for body and subject) and sends using smtp (just like Cardbox uses) a mail to first nn records and repeats this until all clusters of nn records are done.
- For control I mail in every nn also to my own mailaddres.
Option 3+4 does not use Outlook. However it makes your mailcontent flexible with a desired font and a simple send process of max nn addresses - if you can make a format ;-). It is only a macro not for for beginners.
I think option 2+3 is realizable?
regards
bert

Wow, thanks for that.

I like option number 2. That sounds like the easiest way to do this.

Just to make it more difficult, I have 6 different fields for email addresses per record (for different contacts per organisation). Is it possible to:

1) Include these fields in the macro to search and copy to clipboard.
2) Ask it to ignore those fields when it is blank?

For example, one record may only have an email address is on of the boxes, whilst another might have all 6. If it just copied the first 40 fields, I may only end up with 7 or 8 email addressses. Does that make sense?

Try this macro. It generates an email in Outlook with max 39 addreses into your bcc field.
With or without Word as email editor.
Success
Bert

The macro:

'number of max addresses in bcc
maxmail = 39

'you can also pickup this here from a txt file. Do not forget that a vbcrlf or lf must be translated to <br>
emtxt = "This is your BodyText!"

'the subject of your mail
subj = "My Subject"

set em = Records
for ct = 1 to em.count
     'add here as much as emailfields you want; have attention that emailaddresse must be correct spelled (no spaces in it!!)
     'only if in a field is an address is a line to x added.
     if em.item(ct).fields("MAIL1") <> "" then x = x & em.item(ct).fields("MAIL1") & vbcrlf
     if em.item(ct).fields("MAIL2") <> "" then x = x & em.item(ct).fields("MAIL2") & vbcrlf
     if em.item(ct).fields("MAIL3") <> "" then x = x & em.item(ct).fields("MAIL3") & vbcrlf
     if em.item(ct).fields("MAIL4") <> "" then x = x & em.item(ct).fields("MAIL4") & vbcrlf
     if em.item(ct).fields("MAIL5") <> "" then x = x & em.item(ct).fields("MAIL5") & vbcrlf
     if em.item(ct).fields("MAIL6") <> "" then x = x & em.item(ct).fields("MAIL6") & vbcrlf
     statustext = "Collecting addresses " & ct & "/" & em.count
next

'make an array of x
xm = split(x,vbcrlf)

'generate a email after 39 addresses
for ctr = 0 to Ubound(xm)
    addr = addr & xm(ctr) & vbcrlf
    i39 = i39 + 1
    if i39 = 39 then
       fnc = makemail(addr)
       addr = ""
       i39 = 0
    end if
next

'the last addresses
fnc = makemail(addr)

'===================================================
function makemail(addr)
Set Outlook = CreateObject("Outlook.Application")
Set Msg = Outlook.CreateItem(0)
With Msg
     .To = "AllDearReceiversWhoWantToRead@interested.com"
     .cc = ""
     .bcc = addr
     .Subject = mysubj
     .HTMLbody = emtxt
     .Display
end with
end function
'============================================

ps:
If you only want to copy addresses to the clipboard then replace
        fnc = makemail(addr)
for this
       ClipboardText = addr
       pause "There are " & i39 & " adresses on the clipboard",300,300

if i39 = 39 then

should be

    if i39 = maxmail then

regards
bert

Bert, you are an absolute legend.

That is working perfectly, and does exactly what I needed it to do.

Thank you so much for your help, I really do appreciate it. This bulk email stuff has been an issue for me and for my equivilent in our sister office for a long time now.

Thank you again.

Hi bert,

I am going to be greedy and ask for more help with refining this.

Is it possible for the macro to ask for one (or possibly multiple) attachments before generating the emails so that every email is created with attachments already on?

I have tried with the "sender.AttachFile("c:\path\filename.typ")" I found in the help file, but I cannot get it to work.

Hi,
You can pick up one or more files.
I think you want to pickup the files for all recipients the same?
Please confirm.
I'me now on a place with a very poor inet connection. So my answer can be delayed.
regards
bert

It will be the same files for every recipient, but sometimes it may be 1 file, othertimes it may be multiple files.

I was also wondering if it would be possible to edit the body text of the email address before generating the email, but using HTML so I can change the font to Arial and type the email out once rather than copying and pasting into multiple emails.

I basically want to type the email in Arial font, choose attachments (could be 0, could be quite a few), and then have it generate the emails.

Well,
You have to add a little for adding the attachments:

Before
set em = Records
add this for picking up the filesnames:

dlg = GetOpenFilenames("All Files|*.*","","select files (use > 1 ctrl+click!)",cbxGetFilenamesWithPanel)
fles = split(dlg,vbcrlf)

then before .Display add this for add the files:

     for each fle in fles
        .Attachments.Add fle
     next

and it will work.

regards
bert

Thank you Bert, that is working excellently.

To be extra cheeky, is there anyway I can format the text of the email so that I can change the font of the body text to arial?

And how would I then format the body text to include line breaks, bold, website links etc?

I don't suppose there is any way of bringing up a "What You See Is What You Get" formatting box to edit the body text is there?

I suggest var emtxt fill with content of a html file (standard name).
The html file you can make with a simple html editor or even with MS Word (save as).
Only thing is read the html file.
To be continued.
Regards
Bert

Looking back up this thread, the option 4 you mentioned seems like it may be ideal. You wrote:

"My last bulk email I did I developed it did all with a Cardbox/smtp macro.
- That message text is then written to the temp folder and then by the macro to html text.
- Macro reads the html data (for body and subject) and sends using smtp (just like Cardbox uses) a mail to first nn records and repeats this until all clusters of nn records are done."

Does this mean I could then format the email with line breaks/bold text/web links etc without writing it in HTML and have it place it in the emails generated by my macro?