Automatiseren mailing via excel

[spiritguard]

Goedemiddag allen,

Allereerst een kleine uitleg van de situatie:
Ik werk in een levensmiddelenbedrijf op de kwaliteitsdienst. Wij zijn onder andere verantwoordelijk voor het bijhouden van certificeringen van leveranciers, wij moeten ten alle tijden een actuele kopie van de certificaten van onze leveranciers in ons systeem hebben.
Nu is het probleem dat wij heel veel leveranciers hebben, het altijd bijhouden van deze certificeringen, het schrijven van mails ed. kost best wel veel tijd.
Deze tijd kunnen wij goed ergens anders gebruiken en daarom wil ik dit graag automatiseren via Excel.

Mijn vraag:
Onderaan de pagina vinden jullie een screenshot van het bestand.
Wat ik persoonlijk graag wil is het volgende, zodra de datum in kolom E en/of F verloopt moet er een mail worden gestuurd naar het emailadres in kolom B en/of C(kolom c is niet altijd ingevuld).
Het kan ook zo zijn dat de leverancier maar 1 certificaat heeft, het kan ook zo zijn dat ze er 8 hebben.. In dit geval worden de certificeringen verder vernoemd in de opvolgende kolommen.
Om herhaling van mails richting leveranciers te voorkomen wil ik graag werken met kolom A, daar zou de datum van versturen van de laatste mail in moeten komen te staan.
De mail zelf zal ongeveer als volgt opgebouwd worden:

Beste,

Uit onze administratie is gebleken dat de kopie van certificaat ... ... welke wij in bezit hebben is verlopen.
Graag ontvangen wij binnen 14 werkdagen een kopie van het nieuwe exemplaar.

Alvast hartelijk dank.

Met vriendelijke groet,
blablabla

Ook hier wil ik graag weer wat doen. Op de plaats van de puntjes zou ik graag de naam/namen van de certificeringen willen hebben. Deze namen staan bovenaan de kolom waar de datum van de certificering ook in staan.

Relevante software en hardware die ik gebruik
Excel en outlook 2016, daarnaast werken we in een cloud. Dit heeft helaas invloed op andere programma's die wij draaien, daarom dat ik dit hier ook bij vermeld.

Wat ik al gevonden of geprobeerd heb:
Ik ben al een beetje bezig geweest met visual basics dus mijn kennis is niet nul, echter dit gaat mijn petje erg te boven.
Ik wil het graag zelf ook leren dus als jullie een beetje uitleg willen geven bij de code zou ik dat zeer waarderen . Van alleen de code knippen/plakken wordt ik ook niet veel wijzer haha



Ik hoop dat jullie mij kunnen helpen en als jullie meer info nodig hebben hoor ik het graag.

Alvast hartelijk bedankt voor de hulp!

Groet Marc

[bernard038]

Ha Marc,

Dit kan je doen via afdruk samenvoegen in MS Word. Zie voor meer inspiratie:
https://support.office.co...c0-4ba1-9a7b-0a948fa3d7d3

[jeanj]

Maak een extra kolom aan zodat je daar een formule of(nu()>e2-14;nu()>f2-14) inzet. Deze geeft waar als de datum in kolom e of f over 14 dagen is verlopen, onwaar als dit niet het geval is. Je kan een check tovoegen op kolom A . Dus denk aan en(of(nu()>e2-14;nu()>f2-14);A2=“”). Je moet zelf dan wel goed bijhouden of iets is verstuurd in kolom A.

Dan kan je de lijst filteren en via word de brieven maken zoals hierboven beschreven.

Dit betekent wel dat je elke week de lijst moet controleren en bij werken.

[spiritguard]

dank je wel voor de hulp! Ik ga er vandaag mee experimenteren.

jeanj schreef op vrijdag 20 april 2018 @ 18:52:
Maak een extra kolom aan zodat je daar een formule of(nu()>e2-14;nu()>f2-14) inzet. Deze geeft waar als de datum in kolom e of f over 14 dagen is verlopen, onwaar als dit niet het geval is. Je kan een check tovoegen op kolom A . Dus denk aan en(of(nu()>e2-14;nu()>f2-14);A2=“”). Je moet zelf dan wel goed bijhouden of iets is verstuurd in kolom A.

Dan kan je de lijst filteren en via word de brieven maken zoals hierboven beschreven.

Dit betekent wel dat je elke week de lijst moet controleren en bij werken.

De lijst op zich wordt elke week wel meerdere keren geopend voor informatie dus dat is iets waar ik geen rekening mee hoef te houden.

[spiritguard]

Ik ben eens aan de gang gegaan met het afdruk samenvoegen in word maar dit werk denk ik niet voor deze situatie.
Op deze manier kan ik wel bulkmails maken maar deze kan ik niet specifiek richten aan een bedrijf zonder dat ik elke mail apart moet gaan bekijken.
Ik weet dat de mogelijkheid in Excel er is alleen ik weet niet hoe ik dit vorm moet geven in deze specifieke situatie..

[Paul1987]

spiritguard schreef op maandag 23 april 2018 @ 09:20:
Ik ben eens aan de gang gegaan met het afdruk samenvoegen in word maar dit werk denk ik niet voor deze situatie.
Op deze manier kan ik wel bulkmails maken maar deze kan ik niet specifiek richten aan een bedrijf zonder dat ik elke mail apart moet gaan bekijken.
Ik weet dat de mogelijkheid in Excel er is alleen ik weet niet hoe ik dit vorm moet geven in deze specifieke situatie..

Je kunt dmv VBA keurig een mail laten versturen.
Ik heb ooit een script gemaakt dat gebruik maakt van VBA en Powershell. Deze sluit niet geheel aan op jouw wens maar het mail gedeelte werkt wel. Je kunt hier makkelijk een loop omheen bouwen die voor iedere regel die aan jouw criteria voldoet een mail te laten sturen met een aantal uit de tabel opgehaalde gegevens in de tekst. Je kunt hier overigens HTML opmaak aan mee geven. Dus alles wat je in outlook kunt, kun je ook hier in. Mogelijk dat dit script nog mooier kan maar dat laat ik aan jou over

Visual Basic .NET: module1

Sub mail()
'definities vooraf aanmaken
Call disable
Dim wb1
Dim ws1
Dim rng1

Dim cmd
Dim shell
Dim executor
Dim mail

Dim pscommand1
Dim pscommand2
Dim pscommand3
Dim pscommand4
Dim pscommand5
Dim pscommand6
Dim pscommand7
Dim pscommand8
Dim pscommand9

Dim pscommand10
Dim pscommand10A

Dim pscommand11A
Dim pscommand11B
Dim pscommand11C
Dim pscommand11D

Dim pscommand12
Dim pscommand13
Dim pscommand14
Dim pscommand15
Dim pscommand16
Dim pscommand17


Dim body
Dim Body1
Dim Body2
Dim Body3
Dim Body4
Dim Body5

Dim Body6
Dim Body6A

Dim Body7A
Dim Body7B
Dim Body7C
Dim Body7D

Dim Body8
Dim Body9
Dim Body10
Dim Body11
Dim Body12
Dim Body13

Dim Commando
'Opzetten basis informatie + parameters
'basis parameters (workbook, sheet en range)
Set wb1 = ActiveWorkbook
Set ws1 = wb1.Sheets("sheetnaam")

'locatie waar het e-mail adres staat. In jouw geval zul je loopjes nodig hebben om zowel de naam als het certificaat op te pakken en zo meerdere mail berichten te versturen
Set rng1 = ws1.Range("C1")
mail = rng1.Value

'specifieke parameters:
'SMTP server:
pscommand1 = """smtp.server.nl"""

'E-Mail adres (gebruiker uniek)
pscommand2 = """" & mail & """"

'Afzender:
pscommand3 = """Naam Afzender <[e-mail]>"""

'Onderwerp'
pscommand4 = """ONDERWERP"""

'Body (HTML code + tekst) SMTP, powershell heeft altijd een '"' nodig bij een definitie van informatie.
pscommand5 = """<HTML><HEAD><META http-equiv=""" & """Content-Type"""" content=""""text/html; charset=iso-8859-1"""" /><TITLE></TITLE></HEAD>"""
pscommand6 = """<BODY " & "bgcolor=""""#FFFFFF"""" style=""""font-size: Small; font-family: Calibri; color: #000000""""><P>"""
pscommand7 = """Aanhef<br>"""
pscommand8 = """<br>"""
pscommand9 = """ Tekst <br>"""
pscommand10 = """Tekst.<br>"""
pscommand10A = """<br>"""
pscommand10B = """Tekst<br>"""
pscommand11A = """Tekst <br>"""
pscommand11B = """Tekst. <br>"""
pscommand11C = """Tekst. <br>"""
pscommand11D = """<br>"""
pscommand12 = """Tekst.<br>"""
pscommand13 = """<br>"""
pscommand14 = """Tekst<br>"""
pscommand15 = """<br>"""
pscommand16 = """Tekst<br>"""
pscommand17 = """Tekst</b>"""

'Combineren parameters + powersehell code benodigdheden. Powershell gebruikt '$<dim> =' voor het definieren van gegevens. vb: '$smtp ='
Dim smtp
Dim aan
Dim van
Dim onderwerp

smtp = "$smtp" & " = " & pscommand1
aan = "$to = " & pscommand2
van = "$from = " & pscommand3
onderwerp = "$subject = " & pscommand4

Body1 = "$Body = " & pscommand5
Body2 = "$Body += " & pscommand6
Body3 = "$Body += " & pscommand7
Body4 = "$Body += " & pscommand8
Body5 = "$Body += " & pscommand9
Body6 = "$Body += " & pscommand10
Body6A = "$Body += " & pscommand10A

Body7A = "$Body += " & pscommand11A
Body7B = "$Body += " & pscommand11B
Body7C = "$Body += " & pscommand11C
Body7D = "$Body += " & pscommand11D

Body8 = "$Body += " & pscommand12
Body9 = "$Body += " & pscommand13
Body10 = "$Body += " & pscommand14
Body11 = "$Body += " & pscommand15
Body12 = "$Body += " & pscommand16
Body13 = "$Body += " & pscommand17

Commando = "send-MailMessage -SmtpServer $smtp -To $to -From $from -Subject $subject -Body $body -BodyAsHtml -Priority high"

'combinatie van alle parameters smtp, aan, van, onderwerp en body.

'script definieren als 'CMD' zodat deze later gebruikt kan worden om een script te genereren.
cmd = smtp & vbNewLine & aan & vbNewLine & van & vbNewLine & onderwerp & vbNewLine & Body1 & vbNewLine & Body2 _
& vbNewLine & Body3 & vbNewLine & Body4 & vbNewLine & Body5 & vbNewLine & Body6 & vbNewLine & Body6A & vbNewLine & Body7A & vbNewLine _
& Body7B & vbNewLine & Body7C & vbNewLine & Body7D & vbNewLine & Body8 & vbNewLine & Body9 & vbNewLine & Body10 & vbNewLine & Body11 _
& vbNewLine & Body12 & vbNewLine & Body13 & vbNewLine & Commando


'aanmaken van het powershell script op basis van bovenstaande gegevens.
Dim Filepath

'locatie van het script (deze mag niet aangepast worden)
Filepath = Volledig pad waar het powershell script staat (*.PS1)

'script openen en definieren als #2
Open Filepath For Output As #2

'het eerder gedefinieerde 'CMD' wordt hier in het script bestand geplaatst en opgeslagen
Print #2, cmd
Close #2

'script uitvoeren en daarmee de uiteindelijke e-mail versturen
'basis parameters om een script shell te maken waar door excel gewacht kan worden op de status
Dim wsh As Object
Set wsh = VBA.CreateObject("WScript.Shell")
Dim waitOnReturn As Boolean: waitOnReturn = True
Dim WindowStyle As Integer: WindowStyle = 0
Dim Draaien As Long


Dim parameter As String
Dim C1 As String

'shell commando opbouwen
C1 = "powershell -file " & Filepath
'shell commando uitvoeren en daarmee de e-mail versturen
Draaien = wsh.Run(C1, WindowStyle, waitOnReturn)

End Sub

[spiritguard]

Paul1987 schreef op maandag 23 april 2018 @ 11:00:
[...]


Je kunt dmv VBA keurig een mail laten versturen.
Ik heb ooit een script gemaakt dat gebruik maakt van VBA en Powershell. Deze sluit niet geheel aan op jouw wens maar het mail gedeelte werkt wel. Je kunt hier makkelijk een loop omheen bouwen die voor iedere regel die aan jouw criteria voldoet een mail te laten sturen met een aantal uit de tabel opgehaalde gegevens in de tekst. Je kunt hier overigens HTML opmaak aan mee geven. Dus alles wat je in outlook kunt, kun je ook hier in. Mogelijk dat dit script nog mooier kan maar dat laat ik aan jou over

wauw dat ziet er gaaf uit, mijn prioriteiten op het werk zijn even wat verschoven dus ik kan er helaas nog niet mee aan de gang, als ik het ga proberen dan hoor je wel hoe het werkt

[DBoydNL]

Nee, nee! Die VBA is niet nodig.
Ga aan de slag met die kolom die Waar of Onwaar in een cel plaatst in de rij (record) van een bedrijf waarvan de certificatie over twee weken verloopt (of een ander criterium).

In Word kun je je adressenbestand (databestand) filteren zodat alleen de records waar er WAAR in die kolom staat gebruikt gaan worden als data voor een bulk mail naar alleen die leveranciers.
Wanneer je je afdruksamenvoeg document voorziet van een dergelijk filter, wordt die opgeslagen en elke keer dat je het document opent, controleert hij het bijgwerkte en up to date zijnde Excelbestand en laat alleen de records zien die voldoen aan het filter.

Als je voor al je leveranciers email adressen in je Excel bestand hebt staan, kun je de mailmerge zelfs emailen.

David