אי-מייל אוטומטי מתוך "אקסל" - חלק ג'

אי-מייל אוטומטי מתוך "אקסל" - חלק ג'

אז מה היה לנו עד עכשיו? תזכורת:

בחלק א' -

http://meniporat.blogspot.co.il/2012/09/blog-post_24.html

בנינו אי-מייל הנשלח אוטומטית מתוך גליון ה"אקסל".

נמען האי-מייל, תוכן ההודעה וכותרת ההודעה - נלקחים מתוך שדות בגליון ה"אקסל". אפשר לצרף גם קובץ צרופה.

בחלק ב' -

http://meniporat.blogspot.co.il/2012/09/blog-post_29.html

הלקוח ביקש שהטכסט ייושר לימין (או לשמאל) לפי שפת תוכן ההודעה ולפי שפת ממשק "אופיס" שברשות המשתמש.

                                                           µµµ

כעת שב הלקוח בבקשה חדשה:

עד עתה, כל הפתרונות עסקו בנמען אחד.

אבל הלקוח רוצה לשלוח את ההודעה למספר רב של נמענים. יותר מכך, הוא רוצה לבחור מחדש לפני כל משלוח אי-מייל נמענים מסוימים.

הפתרון: 

נוסיף רשימת נמנעים (בעמודה  Cהחל משורה 12), ולידה (בעמודה B) נוסיף אינדיקציה: TRUEאו FALSE.

כל נמען שבתא הסמוך לו מופיע: TRUE - יצורף לרשימת התפוצה באי-מייל. אחרת - לא יצורף לרשימת הנמענים ולא יישלח אליו האי-מייל.

µµµ

למעוניינים, אפשר להוריד את הקובץ מכאן:

https://docs.google.com/file/d/0B3l5X7tRfy6MMGxXTWVLRk9OU00/edit?usp=sharing

שם הקובץ:
SendAutoMail -with attachment - HTMLBODY & Alignment& Multiple addressees- Hebrew Version-New.xlsm

µµµ

אי-מייל אוטומטי מתוך "אקסל" - חלק ב'

אי-מייל אוטומטי מתוך "אקסל" - חלק ב'

כזכור לכם, בפוסט הקודם פיתחנו פתרון לשליחה אוטומטית של אי-מייל מתוך גליון ה"אקסל" - ללא התערבות ידנית.

להזכירכם, אם בפתיחת הקובץ מתקיימים התנאים:

1. תאריך נוכחי (בתא D2) > תאריך קודם (בתא E2)

2. רמת המלאי = 0 (בתא F2)

אזי מתבצעת שליחת אי-מייל אוטומטית - מתוך גליון ה"אקסל".

אפי, מנהל המחסן היה מאד מרוצה מהפתרון, אבל היתה לו בקשה חדשה:

"כאשר אני שולח אי-מייל ותוכן ההודעה הוא באנגלית (בתא C6) - אזי הטכסט צמוד לשמאל והכל תקין.

אבל, אם תוכן ההודעה שאני רוצה לשלוח הוא בעברית - הטכסט העברי אף הוא צמוד לשמאל, מה שהופך אותו לפחות נוח לקריאה. טכסט עברי צריך להיות, כמובן, מיושר לימין."

הפתרון: 

כמובן שהסכמתי עם הלקוח (הוא הרי תמיד צודק...) ועל סמך הפתרון בדרישה הקודמת שלו בניתי את הפתרון הבא:

בממשק המשתמש - לא נעשה שנוי. 4 הפרמטרים נשארו בעינם.

1. כתובת הנמען: בתא C5

2. תוכן ההודעה: בתא C6

3. כותרת ההודעה ("נושא"): בתא C7

4. קובץ צרופה (Attachment): בתא C8.

החלטנו שהטכסט ייושר לימין כאשר מתקיימים שני תנאים:

א. ממשק "אופיס" של המשתמש הוא בעברית

ב. רוב רובו של הטכסט בתוכן ההודעה (פרמטר 2) הוא בעברית.

השנוי העקרי שעשיתי היה בקוד הפונקציה sendAutoMail

1. כדי לאפשר שנוי של ישור הטכסט (משמאל לימין), היה צורך להפוך את הודעת הטכסט ל- HTML

2.כדי לקבוע אם רוב הטכסט בהודעה הוא בעברית, היה צורך לספור את מספר התוים העבריים בהודעה  מתוך כלל ההודעה. אם מספר זה גדול ממחצית מספר התוים בהודעה - אזי הטכסט נחשב לטכסט עברי.

כך, אם כן, יֵרָאֶה טכסט עברי בהודעת האי-מייל בפתרון החדש(מיושרת לימין):

וכך יֵרָאֶה טכסט אנגלי בהודעת האי-מייל (מיושרת לשמאל):

µµµ

למעוניינים, אפשר להוריד את הקובץ מכאן:

https://docs.google.com/open?id=0B3l5X7tRfy6MNm52amV6YVlxT1k

שם הקובץ:

SendAutoMail -with attachment - HTMLBODY & Alignment- Hebrew Version.xlsm

µµµ

הערות:

1 .כדי שתוכלו לבדוק גם אי-מייל שתוכנו באנגלית מבלי לשנות את תוכן תא C6, כתבו את ההודעה באנגלית בתא: I6 והוסיפו את האות: E בתא: B2.

כך תדע התכנית לקחת את ההודעה מתא: I6 במקום מתא: C6

2. יש לשנות את כתובת הנמען בתא C5. בפעם הראשונה כשנפתח הקובץ, האי-מייל לא נשלח לשום מקום, מכיון שכתובת הנמען המופיעה ב-C5 - אינה קיימת....

3. התכנית מציגה תבת הודעה (Message Box), כדי להודיע למשתמש אם משלוח האי-מייל (מתוך "אקסל") הסתיים בהצלחה או שנכשל.

השפה בתבת ההודעה מתאימה לשפה בתוכן ההודעה: אם רוב ההודעה בתא C6  (או בתא I6) הוא באנגלית - תבת ההודעה תוצג באנגלית, אחרת - ההודעה תוצג בעברית.

µµµ

אי-מייל אוטומטי מתוך "אקסל" - חלק א'

אי-מייל אוטומטי מתוך "אקסל" - חלק א'

מנהל מחסן במפעל גדול פנה אלי בבקשת עזרה:

הוא בודק מדי יום קובץ "אקסל" ובו הנתונים הבאים:

א. תאריך נוכחי (בתא D2)

ב. תאריך קודם (בתא E2)

ג. רמת המלאי (בתא F2)

הבקשה: אם בפתיחת הקובץ מתקיימים התנאים:

1. תאריך נוכחי > תאריך קודם

2. רמת המלאי = 0

אזי יש לבצע שליחת אי-מייל אוטומטית - מתוך גליון ה"אקסל".

הפתרון: 

באמצעות רוטינה (SUB) ופונקציה ב- VBA

א. הרוטינה: workbook_open

זוהי רוטינה מובנֵית (built-in) ב"אקסל". היא מתבצעת באופן אוטומטי בכל פעם שנפתחת חוברת העבודה שבה נמצאת הרוטינה.

א.1. הסבר על הרוטינה:

הרוטינה מקבלת  4 פרמטרים:

1. כתובת הנמען: בתא C5

2. תוכן ההודעה: בתא C6

3. כותרת ההודעה ("נושא"): בתא C7

4. קובץ צרופה (Attachment): בתא C8. פרמטר זה הוא אופציונלי, כלומר: אפשר לשלוח את האי-מייל גם ללא צרופה.

פרמטרים 1,2,3 - לא נבדקים. אפשר כמובן לשלוח הודעת אי-מייל ללא תוכן (פרמטר 2) וגם ללא כותרת (פרמטר 3).

אבל, אם לא מופיעה כתובת הנמען (פרמטר 1) - הפונקציה שאליה פונה הרוטינה, לא תשלח את האי-מייל.

פרמטר 4:

אם מצוין שם קובץ בתא C8 -

א. יש לציין את המסלול המלא של הקובץ (full path), כולל כונן (drive), ספריה (folder) וסיומת הקובץ (suffix). למשל:

D:\Test\רשימת תקלות.xlsx

ב. התכנית בודקת את קיום הקובץ ואם שם הקובץ איננו תקין/לא נמצא במסלול המצוין - האי-מייל לא יישלח ותופיע על כך גם הודעה.

אם כל הפרמטרים תקינים, הרוטינה פונה לפונקציה ומעבירה לה את ארבעת הפרמטרים.

ב. הפונקציה: sendAutoMail

הפונקציה מקבלת את 4 הפרמטרים, מציבה את ערכי הפרמטרים בשדות המתאימים ושולחת את האי-מייל.

אם משלוח האי-מייל הצליח, מוצגת ההודעה: "האי-מייל נשלח בהצלחה"

וכך נראית הודעת האי-מייל לאחר שנשלחה:

µµµ

למעוניינים, אפשר להוריד את הקובץ מכאן:

https://docs.google.com/open?id=0B3l5X7tRfy6MTjBzUGUzdzR1anc

שם הקובץ:

SendAutoMail -with attachment - Hebrew Version.xlsm

µµµ

ערבית ושפות אחרות

ערבית ושפות אחרות

מלים רבות בערבית משמשות בשפות אירופיות, וביניהן גם בשפה האנגלית.

בפוסטים קודמים הראינו מספר דוגמאות של מלים בערבית המופיעות באנגלית, למשל:

arsenal - دار ٱلصِنَاعَة - דַארֻ סִנַאעָה -(בית מלאכה) 

Jerboa - يَرْبُوع [יַרְבּוּעַ]יַרְבּוּעַ (מִין עַכְבָּר מִדְבָּרִי)

Giraffe - زُرَافَة/ زَرَافَة [זֻרַאפָה/זַרַאפָה] גִּ'ירָף, גִּ'ירָפָה

Gazelle - غَزَال [עַ'זַאל]   צְבִי, עֹפֶר 

       

אבל הערבית לא רק "נתנה" אלא גם "לקחה". מלים רבות בערבית מקורן בשפות אחרות, ואפילו כמובן בעברית.

להלן מספר דוגמאות:

·        أجندة - יוֹמָן, פִּנְקַס (agenda) (מלטינית:  agenda  - דברים שצריך לנהל)

·        أخطبوط - תְּמָנוּן (achtabut) (מיונית: "אוקטופוס". "אוקטו"= שמונה, "פוס" = רגל)

·        أَدُون - אָדוֹן (גם במובן: אֱלֹהִים) (adon) (מעברית, כמובן)

·        أَسْمَنْت - בֶּטוֹן, מֶלֶט (isment) (מלטינית: caementum - אבן מחצבה)

·        بَنْطَلُون  - מִכְנָסַיִם (bantalon) (מאיטלקית: pantalone - דמות מצחיקה מה- commedia dell'arte)

·        قَمِيص - חֻלְצָה, כְּתֹנֶת (qamis) (מצרפתית: chemise)

µµµ

VLOOKUP ברבוע - "תוכנה" לנִהול הזמנת חדר במלון

VLOOKUP ברבוע  - "תוכנה" לנִהול הזמנת חדר במלון

שולי מנהלת משרד נסיעות המטפל, בין השאר, בהזמנות לבתי-מלון באילת.

היא פנתה אלי יום אחד ובקשה שאכתוב לה תוכנה המאפשרת את ניהול ההזמנות.

הבעיה שהציגה לפני היא כזו:

יש לה רשימת מלונות באילת. לכל מלון תעריף רגיל ותעריף קיץ.

אם לקוח מזמין חדר במלון בתקופה שאיננה מוגדרת כתקופת הקיץ - יש לחייב אותו בתעריף הרגיל בלבד. אך אם הלקוח מעונין להזמין חדר בתקופת הקיץ - יש להוסיף לחיוב את תעריף הקיץ.

ה"תוכנה" שפיתחתי עבורה נכתבה כולה ב"אקסל". היא מכילה 4 טבלאות דינמיות ונוסחא אחת - השואבת נתונים מתוך הטבלאות לפי 2 פרמטרים.

הנתונים לפרמטרים נלקחים ממה שמקליד הפקיד ברשימת ההזמנות (בדוגמא שלנו: A17:C21. הרשימה תלך ותגדל, כמובן,  ככל שתִוָּסֵפְנָה הזמנות).

בכל שורה ברשימה הוא מקליד שני נתונים:

1. תאריך ה- check-in (בתא: A17)

2. המלון שנבחר מתוך רשימת המלונות (בתא: B17)

רשימת הזמנות

התוצאה מחושבת בתא C17.

בדוגמא שלנו, הפקיד הקליד את התאריך: 12/07/2012 ואת שם המלון: "הילטון". המחיר המחושב ליום הוא 430 ש"ח. (הסבר על שיטת החִשּוּב - בהמשך)

א. הסבר על הטבלאות

טבלה 1: טבלת קודי תעריפי המלון

עבור כל מלון בטבלה (עמודה A) מופיעים שני קודי תעריף:

            א. קוד תעריף רגיל (עמודה B)

            ב. קוד תוספת קיץ (עמודה C)

טבלה 2: טבלת תעריפים רגילים

בטבלה זו נמצא את התעריף הרגיל לפי קוד התעריף מטבלה 1.

קוד התעריף מופיע בעמודה E והתעריף עצמו (מחיר ליום) - בעמודה F

למשל:

אם בטבלה 1 עבור מלון "הילטון" קוד התעריף הוא A - אזי התעריף הרגיל ליום במלון זה הוא: 230 ש"ח ליום (הסכום המתאים לקוד תעריף Aבטבלה 2).

אם בטבלה 1 עבור מלון "אגמים" קוד התעריף הוא H - אזי התעריף הרגיל ליום במלון זה הוא: 600 ש"ח ליום (הסכום המתאים לקוד תעריף Hבטבלה 2).

טבלה 3: טבלת תעריפי קיץ

בטבלה זו נמצא את תוספת הקיץ לפי קוד תוספת הקיץ מטבלה 1.

למשל:

אם בטבלה 1 עבור מלון "הילטון" קוד תוספת הקיץ הוא Q - אזי תוספת הקיץ ליום במלון זה היא: 200 ש"ח ליום (הסכום המתאים לקוד תעריף Qבטבלה 3).

אם בטבלה 1 עבור מלון "אגמים" קוד תוספת הקיץ הוא B - אזי תוספת הקיץ ליום במלון זה היא: 100 ש"ח ליום (הסכום המתאים לקוד תעריף Bבטבלה 3).

טבלה 4: טבלת תקופת הקיץ

טבלה זו מגדירה את תקופת הקיץ, כדי לקבוע אם יש לגבות תוספת קיץ עבור ההזמנה. 

אם תאריך ה- check-in המבוקש חל בתקופה המוגדרת כתקופת הקיץ, אזי יש להוסיף לתעריף הרגיל את תוספת תעריף הקיץ (ראה טבלה 3).

אחרת - התעריף ליום עבור ההזמנה יהיה זהה לתעריף הרגיל בלבד.

טבלה זו, בנגוד לקודמותיה, אינה משמשת בנוסחא לחפוש ערך אלא כדי לקבוע אם יש לגבות מהלקוח תוספת עבור תעריף קיץ.

ב. הסבר על הנוסחא

=VLOOKUP(VLOOKUP($B17,$A$3:$C$7,2,0),$E$3:$F$7,2,0)+VLOOKUP(VLOOKUP($B17,$A$3:$C$7,3,0),$E$11:$F$13,2,0)*AND($A17>=$A$11,$A17<=$B$11)

הנוסחא מורכבת משני חלקים:

חלק אחד מחשב את התעריף הרגיל

והחלק השני מחשב את תוספת הקיץ.

שני החלקים מחוברים בסימן (+)

ב.1. חלק א-חישוב התעריף הרגיל:

אנו משתמשים כאן פעמייםבפונקציה VLOOKUP (מה שקראתי VLOOKUP  ברבוע.. או VLOOKUP של VLOOKUP)

הפונקציה VLOOKUP היא פונקצית חִפּוּש.  היא מקבלת 4 ארגומנטים ומחזירה את תוצאת החִפּוּש. (בסוגריים: ערך הארגומנט). בחפוש הראשון,אנו מבקשים למצוא בטבלה 1 את קוד התעריף של מלון "הילטון".

ארגומנט 1. מה לחפש - המלון המבוקש (הילטון) [תא: B17]

ארגומנט 2. היכן לחפש - בטבלה מס. 1 - טבלת קודי תעריף ($A$3:$C$7)

ארגומנט 3. באיזו עמודה נמצאת התוצאה (קוד התעריף) - בעמודה B, העמודה השניה בטבלה (2)

ארגומנט 4. האם אנו רוצים תוצאה מדויקת או מקורבת - בחפוש קוד תמיד נבקש תוצאה מדויקת - (0)

ותוצאת החפוש: הערך A (בתא: B3) - שהוא קוד התעריף של מלון "הילטון".

וכעת לקטע המעניין: תוצאת החפוש של ה- VLOOKUP הראשון,

(כלומר: קוד התעריף A)  תשמש אותנו ב- VLOOKUP השני.

כעת יתבצע החפוש בטבלה 2 - טבלת תעריפים רגילים:

ארגומנט 1. מה לחפש - קוד תעריף (A) [תוצאת החפוש של ה- VLOOKUP הראשון]

ארגומנט 2. היכן לחפש - בטבלה מס. 2 - טבלת תעריפים רגילים ($E$3:$F$7)

ארגומנט 3. באיזו עמודה נמצאת התוצאה (סכום התעריף) - בעמודה F, העמודה השניה בטבלה (2)

ארגומנט 4. האם אנו רוצים תוצאה מדויקת או מקורבת - בחפוש תעריף תמיד נבקש תוצאה מדויקת - (0)

ותוצאת החפוש: הערך 230 - שהוא סכום התעריף הרגיל של מלון "הילטון".

ב.2. חלק ב-חישוב תעריף קיץ:

גם כאן נשתמש פעמייםבפונקציה VLOOKUP

ראשית, נחפש בטבלת קודי תעריפים (טבלה מס. 1) את קוד תוספת הקיץ שמתאים למלון שבחרנו

ארגומנט 1. מה לחפש - ללא שנוי: המלון המבוקש (הילטון) [תא: B17]

ארגומנט 2. היכן לחפש - ללא שנוי: התחום ($A$3:$C$7)

ארגומנט 3. באיזו עמודה נמצאת התוצאה (קוד התוספת) - בעמודה C, העמודה השלישית בטבלה (3)

ארגומנט 4. האם אנו רוצים תוצאה מדויקת או מקורבת - בחפוש קוד תמיד נבקש תוצאה מדויקת - (0)

תוצאת החפוש: הערך Q (בתא: C3) - שהוא קוד תוספת הקיץ של מלון "הילטון".

כעת יתבצע החפוש בטבלה 3 - טבלת תוספת קיץ:

1. מה לחפש - קוד תעריף (Q) [כאמור, תוצאת החפוש של ה- VLOOKUPהראשון, כמו בחלק הראשון]

2. היכן לחפש - בטבלה מס. 3 - טבלת תעריפי  קיץ ($E$11:$F$13)

3. באיזו עמודה נמצאת התוצאה (סכום התעריף) - בעמודה F, העמודה השניה בטבלה (2)

4. האם אנו רוצים תוצאה מדויקת או מקורבת - בחפוש תעריף תמיד נבקש תוצאה מדויקת - (0)

הפונקציה מחזירה את הערך 200 - שהוא סכום תוספת הקיץ של מלון "הילטון".

                                                           

כעת, כשמצאנו את תוספת הקיץ - נשאר לברר רק דבר אחד. כיצד נוכל לדעת מתי יש לחייב את הלקוח בתוספת הקיץ ומתי - אין לחייב אותו בתוספת זו?

הפתרון לכך פשוט: עלינו למצוא אם תאריך ה- check-in של הלקוח חל בתקופה המוגדרת כתקופת הקיץ (כלומר, לפי טבלה 4: האם תאריך זה חל בין 01.04.2012 ובין 30.09.2012).

הבדיקה נעשית בעזרת הפונקציה: AND. כדי שיתקיים התנאי (תאריך חל בתקופה) הוא חייב להיות לא קטן (>=) מתאריך תחילת תקופת הקיץ (בתא A11) וגם לא גדול (=>) מתאריך סיום תקופת הקיץ (בתא B11).

כפי שאפשר לראות, תוצאת בדיקה זו נותנת רק אחת משתי האפשרויות: התאריך חל בתקופה (TRUE), התאריך אינו חל בתקופה (FALSE).

ב"אקסל" TRUE ערכו הבוליאני = 1, ו- FALSE - ערכו = 0.

לכן, את הסכום שקבלנו כתוצאת תוספת הקיץ (באמצעות ה- VLOOKUP ברבוע) נכפיל בערך הבוליאני של בדיקת התאריך (1 או 0).

אם התאריך חל בקיץ, תוספת הקיץ מוכפלת ב- 1ונוספת לתעריף הרגיל.

אם התאריך אינו חל בקיץ, תוספת הקיץ מוכפלת ב- 0 והוספת 0 לסכום- לא משנה את התוצאה. הלקוח יחויב רק בתעריף הרגיל.

בדוגמא שלנו [הזמנה בשורה 17: תאריך check-in= 12/07/2012, מלון=הילטון]

1. מצאנו שתעריף רגיל של מלון הילטון הוא - 230 ש"ח.

2. מצאנו שתוספת הקיץ עבור מלון הילטון היא 200 ש"ח.

3. מכיון שתאריך ה- check-in חל בקיץ, הלקוח ישלם עבור יום במלון הילטון:

230 + 200* 1 = 430

דוגמא נוספת [ההזמנה בשורה 18: תאריך check-in= 14/03/2012, מלון=אגמים]

1. תעריף רגיל של מלון אגמים הוא - 600 ₪

2. תוספת הקיץ של מלון אגמים היא - 100 ₪.

3. מכיון שתאריך ה- check-in אינו חל בקיץ, הלקוח ישלם עבור יום במלון אגמים:

600 + 100 * 0 = 600

הטבלה הבאה מסכמת את שלבי התהליך:

♠♠♠

לסכום, הראינו כיצד אפשר להשתמש ב"אקסל" לנהול הזמנת חדרים בבתי-מלון. מובן ששיטה זו אינה יכולה להוות תחליף למערכת נהול הזמנות, אבל היא מספקת כלי פשוט וקל לבצוע בהזמנת חדרים בבתי-מלון שונים לפי תעריפים שונים ולפי פרמטרים אחרים לחיוב (בשיטה שלנו: תעריף הקיץ הנקבע לפי תאריך ה- check-in)

שיטה זו היא שיטה גמישה, כי היא מאפשרת שינויים מיידיים - הנעשים בטבלאות בלבד - מבלי לשנות את הנוסחא. לטבלת קודי תעריף אפשר להוסיף מלונות חדשים, עם תעריפים חדשים וקודים חדשים. אפשר גם לשנות את הגדרת תקופת הקיץ: את משך התקופה ו/או את תאריך ההתחלה ותאריך הסיום.

יתרון נוסף: הפקיד אינו נדרש להקליד את שם המלון בהזמנה. שמות המלונות מופיעים כרשימה נפתחת (drop-down list) באימות נתונים (Data Validation) והפקיד יכול לבחור את המלון הרצוי.

[הסבר על "אימות נתונים"- בקרוב]

                                                «««

אני מקוה שתפיקו תועלת מן המידע המופיע כאן.