Emailing Random PIN numbers

[bowlesstan]

Hi this is my first post, I'm sorry if this has been asked before. Ok here goes.

Every evening at Approx 23:00 We send an email to our security depts with a daily pin, this is a random 4 digit code which which will be used code in our door access. We make up the numbers on a daily basis and Email these to three different internal departments. How would I go about getting excel to do this via a macr or something similar. Or am I just whistling in the wind.
Any help would be appreciated

 

[RedBeard]

This is doable, but first, what email software are you using to send these messages?

 

[bowlesstan]

Thanks for replying, we are using outlook 2016 to send the daily Pin.

 

[RedBeard]

The below should work:

Private Sub send_pin()

Dim OutApp As Object
Dim OutMail As Object
Dim strbody As String

Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)

strbody = CInt(((9999 - 1000) * Rnd) + 1000)

On Error Resume Next

With OutMail
    
    .To = "someone@somewhere.com"
    .Subject = "Subject goes here"
    .Body = strbody

.Send

End With

On Error GoTo 0

Set OutMail = Nothing
Set OutApp = Nothing

End Sub

 

[bowlesstan]

Sorry for being a noob but how do I execute this VBA script I am completely new to excel and VBA Scripting. Thanks

 

[RedBeard]

Not a problem, read the following: https://msdn.microsoft.com/en-us/library/office/ee814737(v=office.14).aspx

The link is for Excel 2010, but it's exactly the same in Excel 2016.

 

[bowlesstan]

Hi thanks for the reply - I've added a Click Box to execute and able to send For example a pin with subject Daily A PIN and a random 4 digit number to a number of recipients. Ok so how do I click on the box in excel which I've aptly named DAILY PIN do the same but this time send a random number with a different subject i.e. DAILY A PIN , DAILY B PIN and DAILY C PIN and each with different recipients ONE CLICK DOES ALL. Any help or advice would be appreciated.

 

[mrmmickle1]

Try something like this:

Private Sub send_pin()

    Dim OutApp As Object
    Dim OutMail As Object
    Dim strbody As String
    Dim arrTo As Variant
    Dim arrSubject As Variant
    Dim intLp As Integer
    
    arrTo = Array("me1@email.com", "me2@email.com", "me3@email.com")
    arrSubject = Array("PIN# 1", "PIN# 2", "PIN# 3")

    For intLp = 0 To 2
        Set OutApp = CreateObject("Outlook.Application")
        Set OutMail = OutApp.CreateItem(0)
        
        strbody = CInt(((9999 - 1000) * Rnd) + 1000)
        
        On Error Resume Next
        
        With OutMail
            
            .To = arrTo(intLp)
            .Subject = arrSubject(intLp)
            .Body = strbody
        
        .Send
        
        End With
        
        On Error GoTo 0
        
        Set OutMail = Nothing
        Set OutApp = Nothing
    Next intLp


End Sub

 

[bowlesstan]

Thanks for your help mrmmickle1 that worked like a charm

 

[mrmmickle1]

Glad to help!