VBA Email Loop for excel reporting -


i created macro run report selected indivdual , send person email package. report meant 20 people, has been tedious run report each person , send it.

i wondering if there way type names in cells of people want run report , have excel loop through each 1 , send report selected individual , loop next one.

is possible, , if how go creating macro.

thank aid

perhaps can adjust code below needs. send contents of textbox on activesheet list of emails in column a. use have set sending email account in outlook.

option explicit 'how send email list of recipients based on data 'stored in workbook. recipient email addresses must 'be in column a, , body text of email must in 'the first text box on active sheet.  sub sample()    dim olapp object, olmailitm object, integer, j integer    dim r range, s string, numrows integer, numcols integer    dim dest variant, emailaddr string, txtbox shape    'create outlook application , empty email.    set olapp = createobject("outlook.application")    set olmailitm = olapp.createitem(0)    set txtbox = activesheet.shapes.addtextbox(msotextorientationhorizontal, _    200, 100, 400, 100)    '.textframe.textrange.text = "test box"    'using email, add multiple recipients, using list of addresses in column a.    set r = range("b1")    s = "": numcols = 4: numrows = 4    j = 1 numcols     = 1 numrows       if > 1 s = s & vbtab       s = s & r.offset(j, i)     next     s = s & vbcr    next j    txtbox.textframe2.textrange.characters.text = s    olmailitm        emailaddr = ""        = 1 worksheetfunction.counta(columns(1))            if emailaddr = ""                emailaddr = cells(i, 1).value            else                emailaddr = emailaddr & ";" & cells(i, 1).value            end if        next      'do additional formatting on bcc , subject lines, add body text spreadsheet, , send.        .bcc = emailaddr        .subject = "fyi"        '.body = txtbox.text        .body = activesheet.textboxes(1).text        .send    end     'clean outlook application.    set olmailitm = nothing    set olapp = nothing end sub 

Comments

Popular posts from this blog

php - Vagrant up error - Uncaught Reflection Exception: Class DOMDocument does not exist -

vue.js - Create hooks for automated testing -

Add new key value to json node in java -