Witam wszystkich czytelników bloga w Nowym Roku! W pierwszym tegorocznym poście przedstawię bardzo ciekawą funkcjonalność Worda, który w połączeniu z Excelem i Outlookiem, pozwala wysyłać spersonalizowane e-maile do wielu adresatów. Wyobraźmy sobie sytuację, w której musimy wysłać maila, o takiej samej treści, do wielu firm zakładając, że w treści maila muszą znajdować się informacje dotyczące konkretnej firmy. W naszym przykładzie z zadaniem zmierzy się recepcjonistka hotelu, która chce poinformować firmy, o numerach pokoi, przypisanych poszczególnym pracownikom. Zapraszam do lektury.
1. Lista gości w Excelu
Lista firm, które wykupiły pokoje hotelowe dla swoich pracowników, prowadzona jest w Excelu. Na liście znajdują się informacje dotyczące nazwy firmy, imienia i nazwiska jej pracowników oraz numeru pokoju przypisanego dla każdego z nich.
Niestety zaprezentowany układ listy, nie pozwala na wykonanie naszego zadania. Potrzebna będzie zamiana poszczególnych kolumn tabeli na wiersze i złączenie tekstu z nowo powstałego wiersza w jedno pole. Zamierzonego rezultatu nie można osiągnąć w prosty sposób, np. za pomocą tabel przestawnych. Wykonanie przekształceń możliwe jest natomiast przy użyciu podstawowych funkcji Excela oraz jednej funkcji użytkownika. Zacznijmy od stworzenia unikalnej listy naszych firm usuwając z niej zduplikowane nazwy. Kopiujemy listę do komórki G2 i za pomocą ikony Usuń duplikaty z karty Dane usuwamy powtarzające się wartości. Następnie kopiujemy nagłówki naszej tabeli i wklejamy je do komórek w zakresie G1:AB1.
Aby przekonwertować naszą tabelę, do komórki H2 wpisujemy następującą formułę.
=JEŻELI(NR.KOLUMNY(A:A)>LICZ.JEŻELI($A$2:$A$17;$G2)*3;"";PRZESUNIĘCIE(ADR.POŚR(ADRES(PODAJ.POZYCJĘ($G2;$A$1:$A$17;0);2));(NR.KOLUMNY(A:A)-1)/3;MOD(NR.KOLUMNY(A:A)-1;3)))
Pierwszy argument funkcji JEŻELI sprawdza, czy numer aktualnie wybranej kolumny jest większy od iloczynu krotności wystąpienia danej firmy i liczby 3 odpowiadającej ilości kolumn danych dla tej firmy (firma AAA występuje w tabeli 7 razy po 3 kolumny, zatem łącze dane o jej pracownikach zajmują 21 komórek). Jeżeli numer kolumny będzie większy od wspomnianego iloczynu funkcja wstawi puste pole. Jeżeli zaś pierwszy argument nie będzie spełniony, funkcja wyszuka adres komórki z pierwszym wystąpieniem imienia pracownika danej firmy i na jego podstawie, pobierze do kolejnych komórek, dane przesunięte o określoną liczbę wierszy i kolumn. Formuła może wydawać się zawiła, dla tego aby ją na spokojnie prześledzić, polecam zaznaczanie poszczególnych jej argumentów i użycie klawisza F9, dzięki któremu podejrzymy wyliczony wynik. Nasza tabela po zastosowanych przekształceniach, powinna wyglądać jak poniżej.
Przyszedł czas na złączenie danych z poszczególnych firm w jedno pole. Trudność przy wykonywaniu tego zadania wynika z faktu, że po złączeniu dane pracowników mają zostać zaprezentowane w kolejnych wierszach. Aby wykonać to zadanie w module VBA umieszczamy następujący kod funkcji.
Function połącz(zakres As Range, dzielnik As Integer) As String
i = 1
For Each kom In zakres
If i = 1 Then
połącz = kom.Value
ElseIf (i Mod (dzielnik)) = 1 Then
połącz = połącz & vbNewLine & kom.Value
Else
połącz = połącz & " " & kom.Value
End If
i = i + 1
Next kom
End Function
Powyższa funkcja składa się z 2 argumentów. Pierwszy z nich to zakres komórek, które będziemy łączyć a drugi to dzielnik wskazujący co ile komórek ma nastąpić przesunięcie do kolejnego wiersza. Jeżeli wpisany dzielnik jest równy jeden, funkcja łączy ciąg komórek, oddzielając je od siebie spacją. Jeżeli zaś dzielnik będzie większy od jednego, komórki zostają złączone i przesunięte do kolejnego wiersza, co krotność dzielnika. Skorzystajmy zatem z naszej funkcji. Do komórki AC wpisujemy formułę =połącz(H2:AB2;3) i przeciągamy w dół tabeli. Powstały wynik funkcji kopiujemy do kolumny TOTAL wklejając tylko wartości. Kończąc pracę w Excelu, wypełniamy kolumnę e-mail własnymi adresami poczty.
2. Korespondencja seryjna
Korespondencja seryjna, to moim zdaniem bardzo użyteczne narzędzie biurowe. Excel pełni dla niej zazwyczaj funkcję bazodanową, gdyż sama funkcjonalność ukryta jest w Wordzie. W internecie sporo jest materiałów dotyczących opcji dostępnych w korespondencji seryjnej. Możemy wybierać różne formy tworzonej korespondencji, takie jak list, e-mail czy koperta lub też filtrować dostępne zakresy pól danych. W dzisiejszym poście, skupimy się na tworzeniu treści e-maili, które następnie wyślemy do kontrahentów hotelu. W tym celu otwieramy nowy dokument Worda i wpisujemy przykładową treść wiadomości. Treść maila najwygodniej formatować wybierając z karty Korespondencja, grupy Rozpoczynanie Korespondencji Seryjnej --> Wiadomości e-mail. W ten sposób łatwiej jest ustawić poszczególne akapity.
W miejscu kropek, za chwilę pojawią się dane z naszej tabeli. Klikamy na kartę Korespondencja i po rozwinięciu pól ikonki Wybierz adresatów, wybieramy Użyj istniejącej listy a następnie arkusz z naszymi danymi. Po kliknięciu Ok, klikamy ikonkę Wstaw pola koresp. ser. gdzie pojawią się nam wszystkie nagłówki zaimportowanej tabeli. Pierwsze pole Firma1, wstawiamy w miejscu kropek treści komunikatu, natomiast drugie TOTAL, w miejsce listy gości.
Kiedy klikniemy ikonkę Podgląd wyników, zobaczymy dane zaczerpnięte wprost z naszej tabeli. Jeżeli chcielibyśmy dodać określony tekst do stworzonego pola wystarczy dopisać go w formule użytej w Excelu np. &", pokój nr."
Strzałeczki widoczne obok ikony z lupą służą do przewijania wyników. Każde kliknięcie strzałki pobiera dane o pracownikach danej firmy. W ostatnim kroku pozostaje nam już tylko wysyłka naszych wiadomości za pomocą Outlooka. W tym celu klikamy ikonkę Zakończ i scal, a następnie Wyślij wiadomości e-mail.
Jako pole adresata wybieramy kolumnę z naszymi adresami e-mail, uzupełniamy tytuł naszej wiadomości i po otworzeniu Outlooka oraz kliknięciu Wyślij/Odbierz dla wszystkich folderów, nasza wiadomość zostanie wysłana pod wskazane adresy. Plik, na którym pracowaliśmy dostępny tutaj.
One thought on “E-mail do kilku osób z jednakową treścią – korespondencja seryjna”
Dobry artykuł. Super stronka.