MS Excel / VBA help thread

You are reading a single comment by @Chalfie and its replies. Click here to read the full conversation.

•

Chalfie

guyzzz
I'm trying to send 600 emails with an attachment.
I've found a way to do this (google).

i've got something that will work (from here:
https://www.mrexcel.com/forum/excel-questions/514468-mail-merge-email-attachments-vba-2.html)

but I've changed the end to

 With OutMail
                .To = cell.Value
                .Subject = "HELLOWORLD"
                .HTMLBody = strbody
                
 
                For Each FileCell In rng.SpecialCells(xlCellTypeConstants)
                    If Trim(FileCell) <> "" Then
                        If Dir(FileCell.Value) <> "" Then
                            .Attachments.Add FileCell.Value
                        End If
                    End If
                Next FileCell
 
                .Display
                Application.Wait (Now + TimeValue("0:00:02"))
                Application.SendKeys "%s"

However when you send it, there's still a display of the email and it being sent.
What can do?

Especially as it's probably close to 1200 mails ackshually.

•

Sam_w in reply to @Chalfie

no idea if it will work but application.screenupdating = false

?
•

aggi in reply to @Chalfie

Remove .Display and replace with .Send and lose the two lines after that (the two lines are basically wait for two seconds and then send Alt+S which is the keyboard shortcut for send in Outlook, a bit of a workaround for someone who didn't know how to send automatically).
•

aggi in reply to @Chalfie
The whole thing looks a bit complicated unless you're looking to send different attachments. I'd go with something like
```
.Attachments.Add "c:\my attachment"
```
By the way, if you're using gmail you may need to add a pause time in between the emails, it doesn't like mulitple emails going through the server one after another.
•

NotThamesWater in reply to @Chalfie

What mail client? Googlemail & googlesheets can do this silently.
•

chez_jay in reply to @Chalfie

guyzzz

I'm trying to send 600 emails with an attachment

No idea what kind of place you work at, but from experience I hope you've briefed your manager to back you up when they get a call from IT... a lot of places will take a dim view of automated mail from personal accounts, especially to external orgs. Although maybe only when you hit a bug and send out rather more than you expected.

MS Excel / VBA help thread

About

LFGSS