MS Excel / VBA help thread

Thanks.
In the end I used....
WHERE [Date entered]<=DateSerial(Year(Date()), Month(Date()) + 0, 0)

awaits nuclear explosion of
"DAMO WHY??"

Who's Damo? I thought he flounced?

Experience / Skills Needed.
Superior Excel ability
Understanding data analysis and the data requirements for a business
Experience working in data analysis
Experience working with finance and/or sales teams would be great!
Strong interpersonal and communication skills, and previous experience communicating with stakeholders within an organisation

Define: Superior excel ability?

Pivot Tables
VB automation / macro editing
um...
INDEX() and MATCH() ?

Judging by the other criteria, I guess something like being able to use the functions listed here?

Probably knowing the use of F4 and the funny $ symbols would be enough to wow most people.

Is a serious question, because I think I can do some stuff (and for other stuff I ask in here), but "Superior" or "Excellent" put me off. I know what I consider those things and I don't match them.

Using the audit functions. Because you'll probably be unravelling the mess left by the previous Excel person.

Knowledge of function flags like 0 or 1 in vlookup, or -1, 0, 1 in match.

Different VBA functions available between different versions of Excel.

If they wanted an Excel god, they would have specified they need an Excel MVP.

Using the audit functions. Because you'll probably be unravelling the mess left by the previous Excel person.

• check
  
  

Knowledge of function flags like 0 or 1 in vlookup, or -1, 0, 1 in match.

• check
  
  

Different VBA functions available between different versions of Excel.
-hmmm

If they wanted an Excel god, they would have specified they need an Excel MVP.
okes

The job desc is generic enough that it's worth applying for to find out more. Unless you've left out a line that says you need a PhD and 5 years of quant research then I don't think there's anything there to be concerned about yet.

-hmmm

As long as you know enough to know what to google then you're good. Knowing what you're looking for and how to phrase the question is more important than knowing the answer. And you've not asked stupid questions on this thread.

It probably depends on the job. I've seen absolute behemoth spreadsheets, and pretty advanced users, in Finance / Sales.

I wouldn't let it put you off going though - you're an active poster in this thread, so you probably know more excel than 99.9% of people straight away, and certainly know how to find out what you don't know.

Filters obvs

Slicers
Linked tables

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.

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

?

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).

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.

Could be wrong, but from memory I don't think you can do the .Send. I think it asks for confimation, which is why you use the Display / SendKeys to send it for you.

From memory I don't think you can do the .Send. I think it asks for confimation, which is why you use the Display / SendKeys to send it for you.

You can't!

I have a template spreadsheet with fields a:l
a = urn
b = orgname
c= email
d = template file location
e:k = files to be added
l = something else

i can whiz up bits and pieces from that by saying
define cell which has email in it, use offsets to get the other bits, fetch the template file, replace bits in the template file with a,b,l, attach files in columns e:k.
this lets me add diff files (if i need to) to diff orgs but with similar text or something.

anyway.
i don't think it's configured properly to allow 1200 emails out the door without me watching and going click click click or just watching them in display.

yeah that's in the bit I haven't shown!

Thought that might be too obvious!

Is alright, I just nicked this and then spent at least 20 minutes in a meeting counting on my fingers trying to work out offsets....

Could you use .send and disable alerts using

Application.DisplayAlerts = False

Weird, works fine for me on Outlook/Excel 2013. Must be something in the setup somewhere.

Just tested this and works fine

Sub BGEmailLeeh()
 
 
   
    
   Dim OutApp As Outlook.Application
    Dim OutMail As Outlook.MailItem
    
    
    
    
        Set OutApp = CreateObject("Outlook.Application")
        Set OutMail = OutApp.CreateItem(olMailItem)
     
          On Error Resume Next
          ' Change the mail address and subject in the macro before you run it.
         With OutMail
        .To = "test@email.com"
        
        .Subject = " Test email "
        
        .Body = "Dear "
        
        .Attachments.Add "c:\Template.xls"

       '.Display    ' ".Display" allows you to display the email without sending
       .Send       ' ".Send" will send it automatically. Choose which option you want
        End With
    
    On Error GoTo 0

Set OutMail = Nothing
Set OutApp = Nothing
    
End Sub

You need to add the Microsoft Outlook 15.0 Object Library and the Microsoft Forms 2.0 Object Library to your references (numbers may vary)

JUst tried that.
nope.
it's something to do with trust centre and something i'm not really recalling properly. But i imagine i need admin rights.