MS Excel / VBA help thread

urgh as soon as trust centre and admin rights are involved I recommend setting your PC on fire and throwing it out of the window, makes more sense then.

OF course. This looks a shit tonne easier.

https://stackoverflow.com/questions/26811679/sending-email-in-r-via-outlook

mail merge and franking machine?

You could write the Macro in Outlook, may get around the issue. (Although I can't tell as the Excel VBA version works fine for me.)

Private Function EmailTest()
'Reference must be set to the Microsoft Outlook Library



Dim oOutlook As Outlook.Application
Set oOutlook = New Outlook.Application

Dim oMailItem As Outlook.MailItem
Set oMailItem = oOutlook.CreateItem(olMailItem)

With oMailItem
.To = "email@test.co.uk"
.Subject = "Email Gift Test using RE VBA"
.Body = "Gift Info: "
'.Display
'Comment out or delete the above line ".Display" if you want the email to be sent without user intervention.
.Send
End With

Set oMailItem = Nothing
Set oOutlook = Nothing



End Function

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

Outlook.
I can't work outside of office in the office.

Is it because the body of my text is in an HTML doc?

Makes no difference for me

That's good. I've not tried it since excel 2003, when you definitely couldn't do it without sendkeys. We certainly weren't running an international scale reporting solution using it. Ahem.

Sendkeys in general is a pain due to being impossible to debug.

Ok. I'll test on Monday when I'm next in the office....

Thanks.

Guess what gong went off when I reread everything?
"Did you check object library?"

Frustratingly, I've tried both options @aggi has provided, both options require me to click allow.....
do i want to talk with IT?
I mean, really?

There is another option where you can bypass Outlook and set up details of your SMTP server directly in VBA. I've never tried it though as the Outlook method worked better for us
https://www.rondebruin.nl/win/s1/cdo.htm

Yeah I think I'm going to have to look into that a bit later.

I tried the

library(RDCOMClient)


OutApp <- COMCreate("Outlook.Application")


outMail = OutApp$CreateItem(0)

outMail[["To"]] = "government.lackey@gov.uk"
outMail[["subject"]] = "Bruh"
outMail[["body"]] = "Tester"
outMail[["Attachments"]]$Add("AFILE")

outMail$Send()

and got the same trust centre beejebus.

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.

The other option is just to sign up to something like Mailchimp https://mailchimp.com/pricing/entrepreneur/

@Chalfie - is this internal or external mail?

It's external....
Initially it was to send something to 12 organisations.
Then I realised "Boof! I can do that fuck awful piece of work this way" and that goes to, ahem, 600 organisations (with some having 2 email addresses).

I could include the attached file contents in the body of the email and then it's just 600 - 1200 emails with no attachments.

I'd ask what your comms people use for mass emailing. Something like Mailchimp should be able to use 'merge codes' to customise content for individuals. Not sure if to the extent of including different attachments, I'd tend to include different URLs to hosted files

The less I have to do with them the better.

Possibly teaching you to suck eggs, but anyway...

Having Excel email 600 people might well be the only way to solve this particular problem, but in general having apps do what they're not designed for is poor practice.

As you've recently posted a link to a job spec I guess you'll soon get to interviewing for them and you'll give examples of problems solved and the approaches you've taken. You'll need to justify why you've taken one route over another (e.g. mailchimp, some other more robust solution)

An Excel spreadsheet emailing 600 people is cowboy coding and a bit of a nightmare for anyone apart from the person that wrote it to work with (much as it makes sense to you, pity the person that you hand over this spreadsheet to when you leave).

So really just saying be aware of what criticisms there are of the approach you're taking and be prepared to justify them if you need to.

(I say this having worked at a consultancy that made very intricate and impressive looking Excel/VBA monstrosities to produce pretty documents and charts. Pretty as they were, there's no way I'd ever recommend the consultancy to anyone because it was such an awful approach to a problem)

sql heads....help please? trying to measure how "out of date" our sycnronising db is over a 24 hour period. currently I can see a snapshot using

SELECT getdate ( )-max(my_timestamp) from my_table 

but can't think of a way to measure an average over a 24 hour period, for example.

This also works and saves me from holding down F5 (LOL) but is messy

USE [my_database]; 
GO  
WHILE (SELECT getdate()-max(my_timestamp) from my_table) > '1900-01-01 00:00:00.000'  
BEGIN  
   SELECT getdate()-max(my_timestamp) from my_table 
END  
PRINT 'DONE';  

That's really helpful advice. (No sarcasm)

I guess what has happened is I went from a simple mail merge to about 12 email addresses with a single file, to this bigger thing (that I haven't used yet) that I'd like to do perhaps.

I would love to use MailChimp or similar, but I'm guessing that we might be bound by budget/sensitive information rules. And anything that has to go through Comms becomes a different nprocess.

The more I've paused and thought and taken on the advice from here the more I've thought about the process.
Sending an email to connect with the organisation is doable by mail merge, but sending a urn and and a file blindly is not a good idea.

I take on board the cowboy comment....

.